New features in Oracle 11g Database - Reference Partition

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.