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: Bangalore
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: 21808
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: Bangalore
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: 21808
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: 3273
Registered: January 2010
Location: Connecticut, USA
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 messageGo to next message
amitsukte
Messages: 12
Registered: March 2012
Location: Bangalore
Junior Member

Thank you so much...
Re: How to Delete Parent table record [message #630170 is a reply to message #560125] Mon, 22 December 2014 01:53 Go to previous messageGo to next message
girishvk
Messages: 2
Registered: December 2014
Location: Bangalore
Junior Member
In a PROD scenario, disabling constraints will not be possible unless an outage is scheduled for this activity.
I would like to do this:

1. delete the records from child table
2. Try to delete the records in parent table for which child records have been already deleted.

When I try to delete from parent table, internally it again checks the respective child records, which will unnecessarily spend time.
Is there a way to skip the child record validation while deleting from parent table, without disabling constraints in child table?


Regards,

Girish
Re: How to Delete Parent table record [message #630171 is a reply to message #630170] Mon, 22 December 2014 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ If your FK is indexed the cost is very cheap unless you delete thousand parent keys.
2/ Why don't you declare the constraint with ON DELETE CASCADE option? There you'd have nothing to do and think.

Re: How to Delete Parent table record [message #630176 is a reply to message #630171] Mon, 22 December 2014 02:43 Go to previous messageGo to next message
girishvk
Messages: 2
Registered: December 2014
Location: Bangalore
Junior Member
Thanks for quick response Michel.

FK is not indexed. I am trying to delete around 5000 records in parent table.
As its PROD, I will not be able to modify the constraint parameters unless I do this in non PROD and take it further.

In non PROD the records were less, so I was able to do the same operation in very less time, which had just 313 records.

Just wanted to check if there is any option like "novalidate" or skip constraint check" option for delete operation in Oracle 11g.
Re: How to Delete Parent table record [message #630177 is a reply to message #630176] Mon, 22 December 2014 02:51 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, there is no such option.
You have to first index your FK.
And start now the process to change the FK to include ON DELETE CASCADE option.

Previous Topic: Use of set in Oracle
Next Topic: A question about using nested decode
Goto Forum:
  


Current Time: Thu Apr 25 04:45:18 CDT 2024