Reference Partitioning is a new feature in Oracle 11g, it is meant for partitioning the parent and child tables using the foreign key constraint.
SQL> CREATE TABLE PARENT_TAB (
2 COL1 NUMBER(3) PRIMARY KEY,
3 COL2 DATE)
4 PARTITION BY RANGE (COL2) (
5 PARTITION Q1 VALUES LESS THAN (TO_DATE('1-4-2009','DD-MM-YYYY')),
6 PARTITION Q2 VALUES LESS THAN (TO_DATE('1-7-2009','DD-MM-YYYY')));
Table created.
SQL> CREATE TABLE CHILD_TAB (
2 COL1 NUMBER(3),
3 CONSTRAINT CHILD_FK FOREIGN KEY (COL1) REFERENCES PARENT_TAB(COL1))
4 PARTITION BY REFERENCE (CHILD_FK);
PARTITION BY REFERENCE (CHILD_FK)
*
ERROR at line 4:
ORA-14652: reference partitioning foreign key is not supported
SQL> CREATE TABLE CHILD_TAB (
2 COL1 NUMBER(3) NOT NULL,
3 CONSTRAINT CHILD_FK FOREIGN KEY (COL1) REFERENCES PARENT_TAB(COL1))
4 PARTITION BY REFERENCE (CHILD_FK);
Table created.
In Reference Partitioning, specifying NOT NULL for the FOREIGN KEY column in child table is mandatory. Otherwise it will throw the above error.
