CODEWITHSUNDEEP
sqlsql-serversql-server-2005cascading-deletes
Chris
Chris

Reputation: 6325

How can I delete from three tables with foreign keys?

I want to be able to choose a groupID and do a cascading delete through three tables which are found in a MS SQL server database. The tables look like the following:

table 1 - GROUP
-------------------------------------------
groupID | description | etc


table 2 - MEMBER
-------------------------------------------
memberID | name | etc


table 3 - MAPPINGTABLE 
-------------------------------------------
mappingID | groupID | memberID

I'm thinking that since I know the groupID, I could probably select the memberID's from the mapping table based off of the groupID and delete those from the member table, but I usually end up with an error of:

"The DELETE statement conflicted with the REFERENCE constraint...[FK constraint in table]".

Would anyone be able to give me some guidance on the best way to delete from all three of these tables at the same time?

Thanks.

Upvotes: 3

Views: 12218

Answers (5)

RRUZ
RRUZ

Reputation: 136401

in order to use the an cascade delete you must specify a delete rule in the the foreign key. the "ON DELETE CASCADE" option is what you need.

see this example

CREATE TABLE table_child
(
fieldkeyparent int,
field1 INT,
FOREIGN KEY ([fieldkeyparent]) REFERENCES Table_parent
ON DELETE CASCADE)

check this link for SQL Server. Cascading Referential Integrity Constraints

Upvotes: 1

Raj More
Raj More

Reputation: 48016

You are running into Referential Integrity. No worries, RI is your friend. It is meant to protect you.

Based on your structure, you cannot delete from the Member or the Group table if the row that you are attempting to delete has a corresponding row in the MappingTable.

If the system were to allow you to do that, you would have orphan data in the MappingTable without corresponding data in the Member or Group tables. The database is preventing you from deleting the data because a Referential Integrity constraint has been placed on the data using the Foreign Key constraint.

There are options like ON DELETE CASCADE, but they are potentially very deadly and can cause massive data loss. I personally never implement cascading deletes.

You should first remove the rows from the MappingTable and then delete any data from the lookup tables (Member, Group)

Having said that, I must say the following:

  1. Make backups (and ensure you have a valid backup) of your data before you delete it.
  2. Make another backup cause data once deleted is gone forever.
  3. Check with the business / SME to validate that you are doing the right thing by removing the data

Upvotes: 5

Tarka
Tarka

Reputation: 4043

The easiest thing is when you're setting up your foreign keys, you set them to ON DELETE CASCADE

ALTER TABLE mappingtable ADD CONSTRAINT fk_group_id FOREIGN KEY(groupID) REFERENCES group(groupID) ON DELETE CASCADE

The other option is ON UPDATE CASCADE if you wanted to add it, but it wouldn't help here.

What this does is it makes it so when you delete the parent from GROUP it will automatically delete any reference to it in the table MAPPINGTABLE

To delete from users as well, you would need to do a second delete statement.

Upvotes: 0

Kieveli
Kieveli

Reputation: 11075

Cascade deletes are implemented differently in different SQL servers. What are you using for server software?

In oracle, you can specify cascade deletes when you create the tables so they will automatically delete data from other tables when the main record is deleted. Here's an example:

ALTER TABLE MAPPINGTABLE ADD CONSTRAINT FK_GROUPID
    FOREIGN KEY (groupID) REFERENCES GROUP(groupID)
ON DELETE CASCADE
;

Upvotes: 0

Neil Knight
Neil Knight

Reputation: 48547

You need to work your way back up the tables, so start at the very bottom and then delete from table 3 working back to table 1.

Upvotes: 3

Related Questions