How do I use cascade delete with SQL Server?

Question

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 1

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