Dropping tables with foreign constraints

From: Richard Hansen <hansenrd_at_cadvision.com>
Date: 1997/01/18
Message-ID: <32e15294.28704363@news.cadvision.com>#1/1

I'm trying to drop a few highly fragmented tables which have primary key constraints referred to by other tables' foreign key constraints.

Initially I built a script, querying user_constraints, to disable all the constraints on tables which referred to the table that I'd like to drop :

select ' alter table ' || table_name || ' disable constraint ' || constraint_name || ';'
from user_constraints
where r_constraint_name in (select constraint_name

                                             from user_constraints
                                             where table_name =<table>
                                              )

and status != 'DISABLED';

• or something similar to this, I'm not at work now!!

After running this script, and disabling the constraints, I attempted to drop the table only to get the Oracle error 2266 which indicated that there were enabled foreign key constraints using the primary key constraint of the table that I wanted to drop.

Then I queried dba_constraints and built a similar script to the one above but which disabled the constraints of any tables which referred to the primary key of the table that I wanted to drop.

Same steps as above and again the same Oracle error!!

What am I missing? I am fast losing the will to live and would appreciate any guidance on this problem.

Also, Oracle MUST supply some scripts/tools for this type of maintenance ... any suggestions?

Thanks in advance. Received on Sat Jan 18 1997 - 00:00:00 CST