Error trying to make a "DROP TABLE": "ORA-02449: unique / primary keys in table referenced by foreign keys"

Question

Error trying to make a "DROP TABLE": "ORA-02449: unique / primary keys in table referenced by foreign keys"

Navigation

#1 by (1 votes)

2

I happen to be trying to delete the following table of name CITY with the following code:

DROP TABLE CIUDAD;

But when I run the code I get the following error:

ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 -  "unique/primary keys in table referenced by foreign keys"
*Cause:    An attempt was made to drop a table with unique or
           primary keys referenced by foreign keys in another table.
*Action:   Before performing the above operations the table, drop the
           foreign key constraints in other tables. You can see what
           constraints are referencing a table by issuing the following
           command:
           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

THE CITY TABLE IS THE FOLLOWING:

CREATE TABLE CIUDAD(
    COD_CIUDAD NUMBER CONSTRAINT PK_CIUDAD PRIMARY KEY,
    NOMBRE_CIUDAD VARCHAR2(20) CONSTRAINT NOTNULL_NOMBRE_CIUDAD NOT NULL,
    NOMBRE_REGION VARCHAR2(20) CONSTRAINT NOTNULL_NOMBRE_REGION NOT NULL
);

I deleted all the tables with which it is related and still I can not delete it.

any help I would appreciate it.

Greetings.

sql oracle

asked by GOHANCKZ 13.12.2017 в 23:35

source

1 answer

Query to count different (MSSQL Server 2005 Express) MVC Create Controller - DbContext has been disposed

score 1 · Answer 1

I deleted all the tables with which it is related and still I can not delete it.

Obviously, there is still a relationship that you have not identified.

To find which table (s) have a foreign key that depends on your table CIUDAD , you can execute the following query:

select distinct c.table_name
  from user_cons_columns cc
  join user_constraints c
    on c.r_owner = cc.owner
   and c.r_constraint_name = cc.constraint_name
   and c.constraint_type = 'R'
 where cc.table_name = 'CIUDAD';

In any case, with Oracle you can also force the DROP using the clause CASCADE CONSTRAINTS :

DROP TABLE CIUDAD CASCADE CONSTRAINTS;

By doing DROP in this way, Oracle automatically deletes any foreign key that points to the CIUDAD table so that the DROP runs without error.