Home » SQL & PL/SQL » SQL & PL/SQL » How to Delete Parent table record (Oracle 10g,11g)
How to Delete Parent table record [message #560053] Tue, 10 July 2012 04:35 Go to next message
amitsukte
Messages: 12
Registered: March 2012
Location: Hyderabad
Junior Member

Hi Everyone how to delete Parent table records without affecting to child table dependent records?..

Thanks in advance
Re: How to Delete Parent table record [message #560054 is a reply to message #560053] Tue, 10 July 2012 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 16928
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't, as long as child records exist.
Re: How to Delete Parent table record [message #560055 is a reply to message #560054] Tue, 10 July 2012 04:42 Go to previous messageGo to next message
amitsukte
Messages: 12
Registered: March 2012
Location: Hyderabad
Junior Member

So there is not any such facility exists in Oracle?..

Is there any trick to this?..
Re: How to Delete Parent table record [message #560057 is a reply to message #560055] Tue, 10 July 2012 04:48 Go to previous messageGo to next message
Littlefoot
Messages: 16928
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A trick? Well, think about it - you (or someone else) created master-detail relationship for purpose. You simply can't remove parent record if its child records are in a detail table because - if you do that - what records would those children belong to? Poor orphans.

Therefore: delete child records first, then delete their parent.

If you don't need master-detail relationship, drop foreign key constraint and remove ex-parent record without consequences.

Anyway, what problem are you trying to solve?
Re: How to Delete Parent table record [message #560125 is a reply to message #560055] Tue, 10 July 2012 13:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1397
Registered: January 2010
Senior Member
amitsukte wrote on Tue, 10 July 2012 05:42
Is there any trick to this?..


The only thing you can do is:

a) Disable FK
b) Delete parent rows
3) Reenable FK with NOVALIDATE

For example:

SQL> create table parent as select level n from dual connect by level <= 5
  2  /

Table created.

SQL> alter table parent add constraint parent_pk primary key(n)
  2  /

Table altered.

SQL> create table child as select level n from dual connect by level <= 5
  2  /

Table created.

SQL> alter table child add constraint child_fk1 foreign key(n) references parent
  2  /

Table altered.

SQL> delete parent where n > 3
  2  /
delete parent where n > 3
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.CHILD_FK1) violated - child record found


SQL> alter table child disable constraint child_fk1
  2  /

Table altered.

SQL> delete parent where n > 3
  2  /

2 rows deleted.

SQL> alter table child enable novalidate constraint child_fk1
  2  /

Table altered.

SQL> select * from parent
  2  /

         N
----------
         1
         2
         3

SQL> select * from child
  2  /

         N
----------
         1
         2
         3
         4
         5

SQL> insert into child values(5)
  2  /
insert into child values(5)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CHILD_FK1) violated - parent key not found


SQL> 


In general, this should be done when you are the only one working with child table to avoid invalid data coming into table while FK is disabled.

SY.
Re: How to Delete Parent table record [message #560240 is a reply to message #560125] Wed, 11 July 2012 07:58 Go to previous message
amitsukte
Messages: 12
Registered: March 2012
Location: Hyderabad
Junior Member

Thank you so much...
Previous Topic: Get Table Structure
Next Topic: Dynamic sql
Goto Forum:
  


Current Time: Sun May 19 01:58:26 CDT 2013

Total time taken to generate the page: 0.10792 seconds