Menu
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 
465
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.
If you want to add an on delete cascade to an existing foreign key constraint, you are going to need two statements. The first statement will drop the constraint and the second statement will recreate it with the addition of the on delete clause.
In SQL Server Management Studio, go to Options / SQL Server Object Explorer / Scripting, and enable 'Generate script for dependent objects'. Then right click the table, script > drop to > new query window and it will generate it for you. Also works for dropping all objects in a db.
You will need to,
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
