How do I use cascade delete with SQL Server?

sql-server cascading-deletes
Bichvan Nguyen picture Bichvan Nguyen · Jun 7, 2011 · Viewed 635.2k times · Source

I have 2 tables: T1 and T2, they are existing tables with data. We have a one to many relationship between T1 and T2. How do I alter the table definitions to perform cascading delete in SQL Server when a record from T1 is deleted, all associated records in T2 also deleted.

The foreign constraint is in place between them. I don't want to drop the tables or create a trigger to do the deletion for T2. For example, when I delete an employee, all the review record should be gone, too.

T1 - Employee,

Employee ID      
Name
Status

T2 - Performance Reviews,

Employee ID - 2009 Review
Employee ID - 2010 Review

Answer

marc_s picture marc_s · Jun 7, 2011

You will need to,

  • Drop the existing foreign key constraint,
  • Add a new one with the ON DELETE CASCADE setting enabled.

Something like:

ALTER TABLE dbo.T2
   DROP CONSTRAINT FK_T1_T2   -- or whatever it's called

ALTER TABLE dbo.T2
   ADD CONSTRAINT FK_T1_T2_Cascade
   FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

Related questions

Is it possible to delete from multiple tables in the same SQL statement?

It's possible to delete using join statements to qualify the set to be deleted, such as the following: DELETE J FROM Users U inner join LinkingTable J on U.id = J.U_id inner join Groups G on J.G_…

Read More
How do I edit a table in order to enable CASCADE DELETE?

I have a table representing users. When a user is deleted I get: DELETE statement conflicted with the REFERENCE constraint Apparently, CASCADE DELETE is not as easy as I imagined in SQL Server, and the option needs to be added …

Read More
MS SQL "ON DELETE CASCADE" multiple foreign keys pointing to the same table?

I have a problem where i need a cascade on multiple foreign keys pointing to the same table.. [Insights] | ID | Title | | 1 | Monty Python | | 2 | Spamalot | [BroaderInsights_Insights] | broaderinsight_id | insight_id | | 1 | 2 | Basically when either record one or two in the insights …

Read More