[14.2.43.993] Foreign key constraint missing in partitioned table script

Toad for Oracle - Beta
1

I will describe the problem by the following scenario.

  1. Create a partitioned master table:
    CREATE TABLE TEST_MASTER
    (
    A VARCHAR2(8 BYTE),
    B VARCHAR2(8 BYTE),
    C VARCHAR2(8 BYTE),
    DATUM DATE,
    CONSTRAINT TEST_MASTER_PK PRIMARY KEY(A) USING INDEX TABLESPACE EGUB_IND ENABLE VALIDATE
    )
    COMPRESS FOR OLTP
    TABLESPACE EGUB_DAT
    LOGGING
    PARTITION BY RANGE(DATUM)
    INTERVAL ( NUMTOYMINTERVAL(1, 'YEAR') )(
    PARTITION CHANGE_LOG_FIRST
    VALUES LESS THAN (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    COMPRESS FOR OLTP)
    NOCACHE
    RESULT_CACHE (MODE DEFAULT)
    NOPARALLEL
    ENABLE ROW MOVEMENT;

  2. Create a partitioned child table as follows:
    CREATE TABLE TEST_CHILD
    (
    A VARCHAR2(8 BYTE) NOT NULL,
    B VARCHAR2(8 BYTE),
    C VARCHAR2(8 BYTE),
    D VARCHAR2(8 BYTE),
    CONSTRAINT TEST_CHILD_PK PRIMARY KEY(A) USING INDEX TABLESPACE EGUB_IND ENABLE VALIDATE,
    CONSTRAINT TEST_CHILD_RPK FOREIGN KEY(A) REFERENCES TEST_MASTER (A) ENABLE VALIDATE
    )
    NOCOMPRESS
    TABLESPACE EGUB_DAT
    LOGGING
    PARTITION BY REFERENCE(TEST_CHILD_RPK)(PARTITION CHANGE_LOG_FIRST LOGGING COMPRESS FOR OLTP)
    NOCACHE
    RESULT_CACHE (MODE DEFAULT)
    NOPARALLEL
    ENABLE ROW MOVEMENT;

When you now open the script tab for the child table with the script option "List constraints after columns", the foreign key constraint is missing.

Using the option "Single alter table command" ist no solution, because the "Not Null" clause is missing on the foreign key column(s).

The foreign key only misses for partitioned tables. The behavoius is the same in TOAD 14.2 Beta, 14.1 and 13.3.

2

Hi Rainer,

I can reproduce the foreign key constraint missing with "List constraints after columns" and I will fix it.

When I try the "Individual" or "Single" constraint options, the TEST_CHILD_RPK and NOT NULL constraints are included in the create table statement as they should be, and the primary key is created afterwards. I don't see any problem with these.

-John

3

Sorry John, I had a mistake in the script for the TEST_CHILD table. If I leave the NOT NULL clause for column A, the table can be created anyway, but afterwards when using "Individual" or "Single", the NOT NULL for column A is still missing, and the primary key can't be created. And for "Constrainsts after columns" the foreign key constraint is missing, as you could reproduce.

In all cases the table script is not working.

Sorry for my mistake in the first post.

Best regards
Rainer

4

I see. That's pretty tricky.

So in the child table, the referential constraint must be listed after the columns, and whatever columns it references must have a NOT NULL, either by primary key or just the NOT NULL constraint, also listed with the columns. But if there is a NOT NULL up there, the primary key could come later.

This will be fixed soon, but not the next beta.