Re: Drop Table with Foreign Constraints & Indexes

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 25 Sep 2004 12:08:59 +1000
Message-ID: <41562579$0$908$afc38c87@news.optusnet.com.au>

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
Received on Fri Sep 24 2004 - 21:08:59 CDT