The Geek Diary

ORA-14652: reference partitioning foreign key is not supported

by

The Problem

While creating a reference partitioning, below ora error is encountered.

SQL> CREATE TABLE TRANSACTION_DETAILS
(TRAN_DTL_ID NUMBER,
DETAIL VARCHAR2(100),
CONSTRAINT TRAN_DTL_FK
FOREIGN KEY(TRAN_DTL_ID) 
REFERENCES TRANSACTIONS_MAIN(TRAN_ID))
PARTITION BY REFERENCE(TRAN_DTL_FK)  2    3    4    5    6    7  ;
PARTITION BY REFERENCE(TRAN_DTL_FK)
                       *
ERROR at line 7:
ORA-14652: reference partitioning foreign key is not supported

The Solution

Reference partitioning provides the ability to partition a table based on the partitioning scheme of the table referenced in its referential constraint. A table can now be partitioned based on the partitioning method of a table referenced in its referential constraint. Tables with a parent/child relationship can be equipartitioned by inheriting the partitioning key from the parent table without duplication of the key columns. If the parent table is a composite-partitioned table, then the table will have one partition for each subpartition of its parent.

Note: While creating a reference partitioning table, the foreign key must be not null.

Error code: ORA-14652
Description: reference partitioning foreign key is not supported
Cause: The specified partitioning foreign key was not supported for reference-partitioned tables. All columns of the partitioning foreign key must be constrained NOT NULL with enabled, validated, and not deferrable constraints. Furthermore, a virtual column cannot be part of the partitioning foreign key.

SQL> 
CREATE TABLE TRANSACTION_DETAILS
(TRAN_DTL_ID NUMBER NOT NULL,
DETAIL VARCHAR2(100),
CONSTRAINT TRAN_DTL_FK
FOREIGN KEY(TRAN_DTL_ID) 
REFERENCES TRANSACTIONS_MAIN(TRAN_ID))
PARTITION BY REFERENCE(TRAN_DTL_FK)SQL>   2    3    4    5    6    7  ;

Table created.

You May Also Like