Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: April 12, 2017 - 4:47 pm UTC

Last updated: April 14, 2017 - 10:14 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Is there a way I can drop a table without cascade if I have foreign keys pointing to the primary key of the table? Any idea?

and Connor said...

If you *dont* use casacde, what would you expect the child tables to then point to ?

You must either use 'cascade' or explcitly drop the foreign key constraints. 'disable' is not sufficient, eg

SQL> create table t1 ( x int primary key ) ;

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> create table t2 ( y int, constraint fk foreign key ( y ) references t1(x));

Table created.

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


SQL> alter table t2 disable constraint fk;

Table altered.

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



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 18, 2017 - 2:25 pm UTC

Thanks