problem adding cascading delete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem adding cascading delete

Author

Topic

Spacy
Starting Member

1 Post

Posted - 2010-02-05 : 12:32:40

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

52326 Posts

Posted - 2010-02-05 : 12:37:54

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

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 12:41:00

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:44:11

thats a good explanation Kristen

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 12:48:00

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 12:48:52

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources