Re: Drop a table with foreign key constraints
Date: Fri, 30 Jul 2010 22:28:32 +0300
Message-ID: <25259764FF9D49E39871AE4AEC763681_at_teddy>
Hi Mike,
Here is what I have tried. Please tell me what I was doing wrong:
SQL> create table a(id integer constraint a_pk primary key, b clob);
Table created.
SQL> create table b(id integer constraint b_pk primary key, b integer constraint b_fk references a(id) deferrable);
Table created.
SQL> set constraints all deferred;
Constraint set.
SQL> drop table a;
drop table a
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL>
I have also tried to add the deferrable attribute to the primary key constraint of the table a:
SQL> create table a(id integer constraint a_pk primary key deferrable, b clob);
Table created.
SQL> create table b(id integer constraint b_pk primary key, b integer constraint b_fk references a(id) deferrable);
Table created.
SQL> set constraints all deferred;
Constraint set.
SQL> drop table a;
drop table a
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL>
Thanks.
Octavian
- Original Message ----- From: "Mike Haddon" <m.haddon_at_tx.rr.com> To: <orasnita_at_gmail.com> Cc: "oracle-l" <oracle-l_at_freelists.org> Sent: Friday, July 30, 2010 5:11 PM Subject: Re: Drop a table with foreign key constraints
> set constraints all deferred; > > <drop and recreate table the way you need to. (make sure data is back)> > > set constraints all immediate; > > Mike > > > Octavian Rasnita wrote:
>> 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-lReceived on Fri Jul 30 2010 - 14:28:32 CDT