SEO

Hi,

I would like to seek help dropping a table with foreign keys being
referenced to and referencing other table. Do we disable constraints
first before "drop table constraints cascade"? Would dropping this table
drop other foreign key referenced table too?

Thanks for any comments.

Ste

Ste wrote:

> I would like to seek help dropping a table with foreign keys being
> referenced to and referencing other table.Â*Â*Â*Â*DoÂ*weÂ*disableÂ*constraints
> first before "drop table constraints cascade"?Â*Â*Â*Â*WouldÂ*droppingÂ*this
> table drop other foreign key referenced table too?



DROP TABLE DEPT CASCADE CONSTRAINTS;

The 'cascade constraints' clause will do automatically the bit you would
otherwise have to do manually: disable or drop the foreign key constraint.

The child table in the foreign key relationship can be dropped at any time
without mentioning the constraints at all, because it's the business of
creating orphans that is not allowed. You can create childless parents at
any time without fuss.

Regards
HJR

Ste wrote:

> Hi,
>
> I would like to seek help dropping a table with foreign keys being
> referenced to and referencing other table. Do we disable constraints
> first before "drop table constraints cascade"? Would dropping this
> table drop other foreign key referenced table too?
>
> Thanks for any comments.
>
> Ste

Two suggestions:

1) Check the documentation (http://docs.oracle.com), in the SQL Reference
manual under DROP TABLE;

2) Try a simple test - this should take about 6 lines.

Hint: according to the docco "Specify CASCADE CONSTRAINTS to drop all
referential integrity constraints that refer to primary and unique keys in
the dropped table. If you omit this clause, and such referential integrity
constraints exist, then Oracle returns an error and does not drop the
table."

In other wrds, if you don't want to implicitly drop children tables, don't
include CASCADE CONSTRAINTS ... OR drop the associated constraint.

/Hans

Ste wrote:

> Hi,
>
> I would like to seek help dropping a table with foreign keys being
> referenced to and referencing other table. Do we disable constraints
> first before "drop table constraints cascade"? Would dropping this table
> drop other foreign key referenced table too?
>
> Thanks for any comments.
>
> Ste

DROP TABLE <table_name> CASCADE CONSTRAINTS;

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Hans Forbrich wrote:

> Ste wrote:
>
>> Hi,
>>
>> I would like to seek help dropping a table with foreign keys being
>> referenced to and referencing other table. Do we disable constraints
>> first before "drop table constraints cascade"? Would dropping this
>> table drop other foreign key referenced table too?
>>
>> Thanks for any comments.
>>
>> Ste
>
> Two suggestions:
>
> 1) Check the documentation (http://docs.oracle.com), in the SQL Reference
> manual under DROP TABLE;
>
> 2) Try a simple test - this should take about 6 lines.
>
> Hint: according to the docco "Specify CASCADE CONSTRAINTS to drop all
> referential integrity constraints that refer to primary and unique keys in
> the dropped table. If you omit this clause, and such referential integrity
> constraints exist, then Oracle returns an error and does not drop the
> table."
>
> In other wrds, if you don't want to implicitly drop children tables,

Er, hold your horses!

Oracle *never* drops child tables. It never even drops child *rows*. All the
'cascade constraints' keywords do is to get rid of the *constraint*. Child
rows and tables are left 100% inviolate and unaffected by the use of those
keywords...

> don't
> include CASCADE CONSTRAINTS ... OR drop the associated constraint.

Again, you're implying that 'cascade constraints' does an awful lot more
(and an awful lot more damage) that it actually does.

It does nothing more than drop the associated constraint for you,
automatically, and is therefore quite safe to use.

Regards
HJR



>
> /Hans

Howard J. Rogers wrote:

> Er, hold your horses!
>
> Oracle *never* drops child tables. It never even drops child *rows*. All
> the 'cascade constraints' keywords do is to get rid of the *constraint*.
> Child rows and tables are left 100% inviolate and unaffected by the use of
> those keywords...
>

Quite right .... with due apologies for some rather &^@#%&(% wording, I
submit the following proof to support Howard's assertion:


SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 25 21:13:59 2001

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create table p ( c varchar(2), constraint P_PK PRIMARY KEY (c));

Table created.

SQL> create table c ( c varchar(2), constraint c_fk foreign key (c)
references p(c));

Table created.

SQL> insert into p values ('A');

1 row created.

SQL> insert into c values ('A');

1 row created.

SQL> drop table p;
drop table p
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


SQL> drop table p cascade constraints;

Table dropped.

SQL> desc c
Name Null? Type
----------------------------------------- --------
----------------------------
C VARCHAR2(2)

SQL>