How do I use cascade delete with SQL Server?

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

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

To add "Cascade delete" to an existing foreign key in SQL Server Management Studio:

First, select your Foreign Key, and open it's "DROP and Create To.." in a new Query window.

Then, just add ON DELETE CASCADE to the ADD CONSTRAINT command:

And hit the "Execute" button to run this query.

By the way, to get a list of your Foreign Keys, and see which ones have "Cascade delete" turned on, you can run this script:

SELECT 
   OBJECT_NAME(f.parent_object_id) AS 'Table name',
   COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Field name',
   delete_referential_action_desc AS 'On Delete'
FROM sys.foreign_keys AS f,
     sys.foreign_key_columns AS fc,
     sys.tables t 
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
ORDER BY 1

And if you ever find that you can't DROP a particular table due to a Foreign Key constraint, but you can't work out which FK is causing the problem, then you can run this command:

sp_help 'TableName'

The SQL in that article lists all FKs which reference a particular table.

Hope all this helps.

Apologies for the long finger. I was just trying to make a point.

You can do this with SQL Server Management Studio.

→ Right click the table design and go to Relationships and choose the foreign key on the left-side pane and in the right-side pane, expand the menu "INSERT and UPDATE specification" and select "Cascade" as Delete Rule.

Use something like

ALTER TABLE T2
ADD CONSTRAINT fk_employee
FOREIGN KEY (employeeID)
REFERENCES T1 (employeeID)
ON DELETE CASCADE;

Fill in the correct column names and you should be set. As mark_s correctly stated, if you have already a foreign key constraint in place, you maybe need to delete the old one first and then create the new one.