0

I am trying to drop a table called customer,

DROP TABLE CUSTOMER;

and I get this error:

Error report -
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";

There is no other tables, which is why I am confused.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
N00TS
  • 1
  • 2
  • 1
    The error, and the validation behind it, are partly trying to stop you doing something which might have consequences you hadn't realised. If there are other tables that rely on this one - and there are - then dropping it would leave orphan data in those. If you're creating, dropping and recreating objects as an exercise then this might be fine, and the simplest thing is usually to drop them in the reverse order you created them (if there are no circular references at least). If these are real tables with real data then obviously you need to be more careful... – Alex Poole Oct 18 '22 at 09:49
  • What is the output of the query mentioned in Action section? What investigation did you perform? Having only (very clear) error message without your current data structures doesn't allow to provide anything more than the error message suggests. – astentx Oct 18 '22 at 09:53

2 Answers2

2

As you were told, one (or more) table references table CUSTOMER via referential integrity constraint. You'll have to drop that (these) constraint first, and then drop the customer table.

Which constraint(s) to drop? Check this:

select m.table_name        references_customer, 
       m.constraint_name   to_be_dropped, 
       m.*, d.* 
from user_constraints m join user_constraints d on d.constraint_name = m.r_constraint_name
where m.constraint_type = 'R'
  and d.table_name = 'CUSTOMER'
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

Your table currently has 1 foreign key

Drop the foreign key before dropping your table

See if any other table has a foreign key pointing to some field in your table, drop it

sonnv0812
  • 49
  • 7