RE: Drop a table with foreign key constraints

    Some people seem feel that the order of columns is infinitely important and for some purposes it probably is. But in your case I don't think your going to be able to change it without killing all of the foreign key constraints from other tables. The reason has to do with the data dictionary. Even if you disable the constraint the R_Constraint_Name in DBA_CONSTRAINTS is going to be invalid when you drop the table, so to maintain a consistent data dictionary the RDBMS has no choice but to remove that data. You can query the DBA_CONSTRAINTS to find all of the foreign key constraints on your table with the following:  

select owner, constraint_name, table_name from dba_constraints
where r_constraint_name in (select constraint_name

                                          from dba_constraints
                                          where table_name = '&table'
                                              and owner = '&owner')
 

    Also in future could you please let us know what version of Oracle your using? It could change the answer you get.    

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Octavian Rasnita Sent: Friday, July 30, 2010 3:10 AM
To: oracle-l
Subject: Drop a table with foreign key constraints

Hello,  

I want to make some changes in a table, like changing the order and type of its columns, adding some new columns after or before some specified columns and so on, but I understood that this is not possible in Oracle without re-creating the table.  

Because of this, I want to drop the current table and create a new one, but many other tables have foreign keys that depend on this table, so it can't be dropped easy.  

I know that I could just use
drop table table_name cascade constraints; but this would drop all the constraints from those many tables and it would be hard to re-create them again (and I might miss some of them).  

Is there a way of disabling all the constraints regarding the table that should be deleted, drop the table and then enable the constraints again in an easy way?  

Thanks.  

--

Octavian

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5324 (20100729) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 30 2010 - 07:13:13 CDT