Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Drop Table with Foreign Constraints & Indexes

Re: Drop Table with Foreign Constraints & Indexes

From: Hans Forbrich <news.hans_at_telus.net>
Date: Sun, 26 Sep 2004 04:26:03 GMT
Message-ID: <vxr5d.156014$XP3.35934@edtnps84>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US