CODEWITHSUNDEEP
sql-servert-sql
Arianule
Arianule

Reputation: 9043

Deleting tables with foreign key constraints

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?

Upvotes: 1

Views: 589

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 2

Related Questions