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.

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

more