ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Use the TRUNCATE command to cut in the database inside a table of data, encountered the following error

SQL >TRUNCATE TABLE ESCMOWNER.SUBX_ITEM

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Sometimes the corresponding Chinese error: ORA-02266: table primary key / foreign key only enabled by reference, generally appear the mistake, because the primary key in the table foreign key table referenced by other, cause an error when deleting data.


At this point, you can use the following script to check the table primary key foreign key constraint information.

1: select c1.table_name as org_table_name, 2: c1.constraint_name as org_constraint_name, 3: c1.constraint_type as org_constriant_type, 4: n1.column_name as org_colun_name, 5: c2.table_name as ref_table_name, 6: c2.constraint_type as ref_constraint_type, 7: c2.constraint_name as ref_constraint_name, 8: n2.column_name as ref_column_name 9: from dba_constraints c1, 10: dba_constraints c2, 11: dba_cons_columns n1, 12: dba_cons_columns n2 13: where c1.owner = 'OWNER_NAME' 14: and c1.table_name = 'TABLE_NAME' 15: and n1.constraint_name = c1.constraint_name 16: and n1.owner = c1.owner 17: and c2.constraint_type = 'R' 18: and c2.r_constraint_name = c1.constraint_name 19: and n2.owner = c2.owner 20: and n2.constraint_name = c2.constraint_name;

The query results are shown as follows.:

1: SQL> select c1.table_name as org_table_name, 2: 2 c1.constraint_name as org_constraint_name, 3: 3 c1.constraint_type as org_constriant_type, 4: 4 n1.column_name as org_colun_name, 5: 5 c2.table_name as ref_table_name, 6: 6 c2.constraint_type as ref_constraint_type, 7: 7 c2.constraint_name as ref_constraint_name, 8: 8 n2.column_name as ref_column_name 9: 9 from dba_constraints c1, 10: 10 dba_constraints c2, 11: 11 dba_cons_columns n1, 12: 12 dba_cons_columns n2 13: 13 where c1.owner = 'ESCMOWNER' 14: 14 and c1.table_name = 'SUBX_ITEM' 15: 15 and n1.constraint_name = c1.constraint_name 16: 16 and n1.owner = c1.owner 17: 17 and c2.constraint_type = 'R' 18: 18 and c2.r_constraint_name = c1.constraint_name 19: 19 and n2.owner = c2.owner 20: 20 and n2.constraint_name = c2.constraint_name; 21:  22: ORG_TABLE_NAME ORG_CONSTRAINT_NAME ORG_CONSTRIANT_TYPE ORG_COLUN_NAME REF_TABLE_NAME REF_CONSTRAINT_TYPE REF_CONSTRAINT_NAME REF_COLUMN_NAME 23: -------------- ------------------- ------------------- ---------------- -------------- ------------------- ------------------- 24: SUBX_ITEM PK_SUBX_ITEM P ITEM_ID SUBX_DIMM R FK_SUBX_DIMM ITEM_ID 25:  26: SQL>

Solution: a primary key constraint to disable table, and then enable truncation

1: SQL> ALTER TABLE ESCMOWNER.SUBX_ITEM DISABLE PRIMARY KEY CASCADE; 2:  3:  4: SQL>TRUNCATE TABLE ESCMOWNER.SUBX_ITEM 5:  6: SQL>ALTER TABLE ESCMOWNER.SUBX_ITEM ENABLE PRIMARY KEY; 7:  8: SQL>ALTER TABLE ESCMOWNER.SUBX_DIMM ENABLE CONSTRAINT FK_SUBX_DIMM; 9: 

Matters needing attention: A foreign key is not automatically restore in ENABLE after the primary key (without the cascade option), and therefore require manual ENABLE reference to the key constraint.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Eve at December 06, 2013 - 9:17 PM