Deleting tables with foreign key constraints

Question

I have three tables

GroupTable
GroupId
Name

Each group has a one to many with Users

Users
UserId
Name
GroupId

And each User has one to many with 'Challenges'

Challenges
Name
UserId

I want to be able to delete a group with the users assigned to that specific group

I have tried this where I do manage to delete the Group based on id without getting a foreign key constraint error but ALL the users added to the user table and ALL the challenges get deleted as well

ALTER TABLE GroupTable NOCHECK CONSTRAINT ALL
ALTER TABLE UserTable NOCHECK CONSTRAINT ALL
ALTER TABLE Challanges NOCHECK CONSTRAINT ALL

DELETE FROM GroupTable
WHERE ID = @GroupId

DELETE FROM child
FROM Challanges as child
INNER JOIN UserTable AS parent
ON child.UserId = parent.ID
WHERE parent.GroupId = @GroupId

DELETE FROM parent
FROM UserTable AS parent
WHERE GroupId = GroupId

How can I ammend the above so that I only delete the gropu with the specific Users and there challenges assigned to the Group?

Umm... `DELETE FROM parent FROM UserTable AS parent WHERE GroupId = GroupId` has to be a typo? Surely you want `GroupId = @GroupId`? — Mark Sinkinson, Jun 09 '15 at 09:37

score 2 · Accepted Answer · edited May 23 '17 at 12:21

Do not disable the constraints for this, as it will compromise data integrity.
Either use on delete cascade option on the foreign keys, or delete the data from all three tables in the correct order, inside a single transaction.
To add on cascade delete to an existing foreign key you must use the alter table statement to drop the existing constraint and then add it again with the on delete cascade option:

ALTER TABLE table_name
   DROP CONSTRAINT constraint_name

ALTER TABLE table_name
   ADD CONSTRAINT constraint_name
   FOREIGN KEY (column_name) 
      REFERENCES other_table_name(other_column_name) ON DELETE CASCADE

(of course, this can be done using ssms's design table window)

Deleting the rows from the related tables in the correct order will ensure you will encounter no problems with the existing foreign key constraints, wrapping all the delete statement in a single transaction will ensure you will only delete from every table or none at all:

DECLARE @GroupId int = 5

BEGIN TRY

BEGIN TRANSACTION

DELETE c
FROM Challanges c
INNER JOIN UserTable u ON(c.UserId = u.UserId)
WHERE u.GroupId = @GroupId

DELETE 
FROM Users
WHERE GroupId = @GroupId

DELETE 
FROM GroupTable
WHERE ID = @GroupId

COMMIT TRANSACTION
END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0 
      ROLLBACK TRANSACTION
END CATCH

Deleting tables with foreign key constraints

1 Answers1