How to drop Parent table without deleting or dropping constraints on child table in Oracle

Question

I have two tables one is Parent and Child. Parent table have primary constraint on it and the Child table have a foreign key constraint on it. Now I want to drop the Parent table without deleting or dropping constraints which are there on child table.

I have tried disabling the constraints on both parent and child table and tried to drop the parent table. But still I was not able to drop the Parent table.

And If the delete the primary constraint on the parent delete, then it also drops the foreign key constraint on the child table.

Please if any one can help me out.

Thanks.

I can't think of a reason why you want to drop the parent that has foreign key relations to it. — winkbrace
– winkbrace, Commented Feb 13, 2014 at 11:28

Corrado Piola · Accepted Answer · 2014-02-13 10:38:52Z

1

You can't drop a parent table if you have a child table with a foreign key constraint in place, unless you specify the CASCADE CONSTRAINTS clause:

DROP TABLE P CASCADE CONSTRAINTS;

This command drops the FK constraint too.

answered Feb 13, 2014 at 10:38

Corrado Piola

8791 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Elfentech · Accepted Answer · 2014-02-13 11:24:48Z

1

Deleting a table will necessarily drop all constraints related to this table. if a table is referenced by this table, you will have to drop those constraints first before to avoid rules violation.

*The oracle documentation says: * http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables010.htm#ADMIN01505

The following statement drops the t table:

DROP TABLE t;

If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:

DROP TABLE t CASCADE CONSTRAINTS;

answered Feb 13, 2014 at 11:24

Elfentech

7475 silver badges10 bronze badges

Comments

NavyPier · Accepted Answer · 2015-08-07 09:41:58Z

-1

Weird... if you disabled correctly the constraints you should be able to delete parent table without any error.

:|

answered Aug 7, 2015 at 9:41

NavyPier

451 silver badge5 bronze badges

2 Comments

Wibisono Indrawan Over a year ago

for dropping a table you'll still get an error even if you already disable the constraint

NavyPier Over a year ago

No. If you disable the costraint you can drop the table, thats whats constraints are made, to protect the relations.

Collectives™ on Stack Overflow

How to drop Parent table without deleting or dropping constraints on child table in Oracle

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related