Re: Drop a table with foreign key constraints
- From: "Octavian Rasnita" <orasnita@xxxxxxxxx>
- To: "Mike Haddon" <m.haddon@xxxxxxxxx>
- Date: Fri, 30 Jul 2010 22:28:32 +0300
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@xxxxxxxxx>
To: <orasnita@xxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
