Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Drop Table with Foreign Constraints & Indexes
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> Received on Sat Sep 25 2004 - 23:26:03 CDT