SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 problem adding cascading delete
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Spacy
Starting Member

1 Posts

Posted - 02/05/2010 :  12:32:40  Show Profile  Reply with Quote
I suspect this is a common newbie problem, but I couldn't find the answer. I want to automatically delete rows from a child table when the key in the parent table is deleted. (However, rows from the child table may be explicitly deleted at any time regardless.)

My PARENT_TABLE table has a primary-key column 'item'.
My CHILD_TABLE table has the non-key column 'item'.

I want to make CHILD_TABLE(item) be a foreign key with a constraint so that when I delete a item from PARENT_TABLE, all the referenced rows in CHILD_TABLE go away.

ALTER TABLE CHILD_TABLE
ADD CONSTRAINT fk_item
FOREIGN KEY (item)
REFERENCES PARENT_TABLE (item) ON DELETE CASCADE

But this gives this error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_item". The conflict ocurred in database FOO, table "dbo.PARENT_TABLE", column 'item'.

Looking in SQL-SMSE under the Constraints for either table, I see nothing. I tried doing an ALTER TABLE x DROP CONSTRAINT fk_item, but consistent with what I saw there, there was no such constraint in either table.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/05/2010 :  12:37:54  Show Profile  Reply with Quote
it seems like you've some existing data in CHILD_TABLE which is violating the constraint you're trying to create (having a item value which does exist in PARENT_TABLE). first identify them and remove/modify
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/05/2010 :  12:41:00  Show Profile  Reply with Quote
Worth giving a second thought to Cascading Deletes. Generally developers avoid them because the deletion is out-of-sight and out-of-mind, and usually better to do

DELETE C
FROM PARENT_TABLE AS P
    JOIN CHILD_TABLE AS C
        ON C.ID = P.ID
WHERE P.SomeCol = 'FOOBAR'
--
DELETE P
FROM PARENT_TABLE AS P
WHERE P.SomeCol = 'FOOBAR'

because then the DEV is sure that they meant to do that, and is in control.

The risk is that, as the project grows, the nature of cascade deletes becomes more complex and those that are automated may do so when that was not considered by the DEVS.

Whereas when the DEVs get "Foreign Key Constraint Violation on CHILD_TABLE" during testing when they try to delete from PARENT_TABLE they stop to think why that message came up, and whether they intend for the associated CHILD_TABLE records to be deleted or not; a good example would be that if Child Records exist get explicit permission from the user to delete them, so:

DELETE C
FROM PARENT_TABLE AS P
    JOIN CHILD_TABLE AS C
        ON C.ID = P.ID
WHERE P.SomeCol = 'FOOBAR'
      AND @ExplicitUserPermission = 1
--
DELETE P
FROM PARENT_TABLE AS P
WHERE P.SomeCol = 'FOOBAR'

this will work if explicit permission has been given, or if there are no child records. An error acts as a safeguard for the developer to add some code, rather than wipe out some precious data.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/05/2010 :  12:44:11  Show Profile  Reply with Quote
thats a good explanation Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/05/2010 :  12:48:00  Show Profile  Reply with Quote
Thanks
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/05/2010 :  12:48:52  Show Profile  Reply with Quote
By chance just found another recent post that discusses Cascade Deleted which may say the same thing, but may be worth a read:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139235
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000