Cascade Delete

[jtrembla]

I have the following tables

Version
--------
VersionID
VersionName


VersionGeography
-----------------
VersionGeographyID
VersionID (FK)
GeographyID (FK)


PricingDrugVersion
-------------------
PricingDrugID
VersionID (FK)
PricingDrugName

PricingDrugGroup
------------------
PricingDrugGroupID
PricingDrugID (FK)
VersionGeographyID (FK)
StartingPrice


Now when I tried to delete a version I get a DELETE statement conflicted with the reference constraint FK_VersionGeographyVersionID_VersionVersionID the conflict occured in table VersionGeography column versionID.

Is this because it might be a circular reference?

Do I need to remove the foreign key reference from the VersionGeography table and use a trigger?

 

[mrdenny]

You don't have a circular reference. This is the point of foreign keys. They are designed to prevent you from deleting data from one table which is used by data in another table.

In order to delete a row from the version table you will need to delete the related rows from the VersionGeography and PrivingDrugVersion tables first.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[r937]

you will need to delete the related rows first

alternatively, go back to the foreign keys and declare them with ON DELETE CASCADE




r937.com | rudy.ca

 

[jtrembla]

Well, I had cascade delete on all foreign keys, but it won't allow me to add a cascade delete on VersionGeography, I get message saying more than one cascade path or something to that affect (I am not at work now).

So the cascade delete on all of the above foreign keys does not work.

 

[mrdenny]

Here's what BOL says about cascading deleted.

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[jtrembla]

So my guess is I need to add a trigger to delete the records, and remove the constraint?

 

[mrdenny]

You can probably leave the constraint and put in an INSTEAD OF TRIGGER.

Or stick an active flag on the Version table and modify the UI to not show the versions which are disabled.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog