Deleteting child table records [message #384827] |
Thu, 05 February 2009 06:33  |
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 #384831 is a reply to message #384828] |
Thu, 05 February 2009 06:47   |
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 #384864 is a reply to message #384827] |
Thu, 05 February 2009 08:36   |
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 #384871 is a reply to message #384827] |
Thu, 05 February 2009 09:03   |
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.
|
|
|
|