Home » SQL & PL/SQL » SQL & PL/SQL » Deleteting child table records (10.2.0.1,Windows 2003)
Deleteting child table records [message #384827] Thu, 05 February 2009 06:33 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi all,

I'm wondering if there is a way to lock a table so we can prevent the child table records from being deleted when the parent records are deleted..

I initially thought about DELETE RESTRICT, I'm still researching if this will solve my purpose.

Thanks in advance
Re: Deleteting child table records [message #384828 is a reply to message #384827] Thu, 05 February 2009 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use a foreign key with ON DELETE CASCADE option.

Regards
Michel
Re: Deleteting child table records [message #384830 is a reply to message #384828] Thu, 05 February 2009 06:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Doesn't the OP want the opposite of Cascade - he wants child records not to be deleted.

I think they want ON DELETE SET NULL
Re: Deleteting child table records [message #384831 is a reply to message #384828] Thu, 05 February 2009 06:47 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Michel, I might be reading it wrong, but I think that the OP is looking for the opposite i.e. he currently has ON DELETE CASCADE and he wants to prevent deletion of child rows
Quote:
so we can prevent the child table records from being deleted when the parent records are deleted..

[Updated on: Thu, 05 February 2009 06:47]

Report message to a moderator

Re: Deleteting child table records [message #384842 is a reply to message #384831] Thu, 05 February 2009 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks both, I indeed misread the post and JRowbottom's answer gives the solution.

Regards
Michel
Re: Deleteting child table records [message #384864 is a reply to message #384827] Thu, 05 February 2009 08:36 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thanks for all your replies. Yes, I would like to prevent the child records being deleted.

There is a foreign key constraint on CRI.id to parent table ANJ.id and the application needs the constraint.

For one program, the specification is written such that the program will throw an error when it is unable to delete a record in ANJ table because the records in CRI are not deleted. To test this, we are trying to force the condition so that the program cannot delete the CRI records and so the delete of ANJ records will fail and the program will throw an error.

ON DELETE SET NULL- I thought this will set the values for CRI records to NULL. We dont really want the values to be set to NULL.

Please give me your suggestions. Thank you.
Re: Deleteting child table records [message #384865 is a reply to message #384864] Thu, 05 February 2009 08:43 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
If you must have the constraint, you have 3 choices. Set the child values to null, delete the child rows, do not allow the Parent row to be deleted.
End of story.
Re: Deleteting child table records [message #384867 is a reply to message #384864] Thu, 05 February 2009 08:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
On Delete Set Null will set the FK Column to null - not the whole row.

Re: Deleteting child table records [message #384871 is a reply to message #384827] Thu, 05 February 2009 09:03 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Pablolee, we just want to test this condition in the program to see if it works fine. If it throws an error to see when the parent table records are not able to deleted, which means first the child table records should be not deleted. So, by default if the child table records are unable to be deleted, the program will error out that the parent table records cannot be deleted.

Since this is just for testing purpose, I thought I can use the lock in exclusive mode.

Thanks for your patience.
Re: Deleteting child table records [message #384873 is a reply to message #384871] Thu, 05 February 2009 09:07 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And if you post a test case representing what you have, try to have, try to do we can help you finding how to do it.

Regards
Michel
Previous Topic: How do I combine data from multiple rows into a single row?
Next Topic: function problem (merged 8)
Goto Forum:
  


Current Time: Fri Feb 14 16:31:21 CST 2025