RE: Drop a table with foreign key constraints
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <orasnita@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jul 2010 08:13:13 -0400
Octavian,
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
