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

SQL> DROP TABLE CDR;
DROP TABLE CDR;
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SOLUTION

Check offending reference(s)

SQL> SELECT  TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
     FROM    DBA_CONSTRAINTS
     WHERE   OWNER = 'TEST'
     AND     CONSTRAINT_TYPE='R'
     AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='CDR');

TABLE_NAME     CONSTRAINT_NAME  C R_CONSTRAINT_NAME   STATUS
-------------- ---------------- - ------------------- --------
TRANSACTION    FK_TRANSACTION   R SYS_C0096900        ENABLED

Drop offecdning reference(s)

By just disabling reference(s), it is still not working.

SQL>  alter table TRANSACTION disable constraint FK_TRANSACTION;

Table altered.

SQL> DROP TABLE CDR;
DROP TABLE CDR;
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

Having to drop offending reference(s), then it is working.

SQL>  alter table TRANSACTION drop constraint FK_TRANSACTION;

Table altered.

SQL> DROP TABLE CDR;

Table dropped.

OR

Drop the table with “CASCADE CONSTRAINTS” option.

SQL> DROP TABLE CDR CASCADE CONSTRAINTS;

Table dropped.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.