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

User · Answer

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

User · Answer

If the one to many relationship is from T1 to T2 then it doesn t represent a function and therefore cannot be used to deduce or infer an inverse function that guarantees the resulting T2 value doesn t omit tuples of T1 join T2 that are deductively valid  because there is no deductively valid inverse function    representing functions was the purpose of primary keys     The answer in SQL think is yes you can do it  The answer in relational think is no you can t do it  See points of ambiguity in Codd 1970  The relationship would have to be many-to-one from T1 to T2

User · Answer

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

User · Answer

I think you cannot just delete the tables property what if this is actual production data  just delete the contents that dont affect the table schema

User · Answer

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

User · Answer

First To Enable ONCascade property   1 Drop the existing foreign key constraint  2 add a new one with the ON DELETE CASCADE setting enabled  Ex   IF EXISTS SELECT 1 FROM sys foreign keys WHERE parent object id   OBJECT ID N dbo Response     BEGIN   ALTER TABLE  dbo   Response  DROP CONSTRAINT  FK Response Request     ALTER TABLE  dbo   Response  WITH CHECK ADD CONSTRAINT  FK Response Request   FOREIGN KEY  RequestId   REFERENCES  dbo   Request    RequestId   ON DELETE CASCADE END  ELSE   BEGIN   ALTER TABLE  dbo   Response  WITH CHECK ADD CONSTRAINT  FK Response Request   FOREIGN KEY  RequestId   REFERENCES  dbo   Request    RequestId   ON DELETE CASCADE END   Second To Disable ONCascade property   1 Drop the existing foreign key constraint    2 Add a new one with the ON DELETE NO ACTION setting enabled  Ex   IF EXISTS SELECT 1 FROM sys foreign keys WHERE parent object id   OBJECT ID N dbo Response     BEGIN  ALTER TABLE  dbo   Response  DROP CONSTRAINT  FK Response Request     ALTER TABLE  dbo   Response  WITH CHECK ADD CONSTRAINT  FK Response Request   FOREIGN KEY  RequestId   REFERENCES  dbo   Request    RequestId   ON DELETE CASCADE END  ELSE   BEGIN   ALTER TABLE  dbo   Response  WITH CHECK ADD CONSTRAINT  FK Response Request   FOREIGN KEY  RequestId   REFERENCES  dbo   Request    RequestId   ON DELETE NO ACTION  END

User · Answer

ON DELETE CASCADE It specifies that the child data is deleted when the parent data is deleted   CREATE TABLE products   product id INT PRIMARY KEY    product name VARCHAR 50  NOT NULL    category VARCHAR 25      CREATE TABLE inventory   inventory id INT PRIMARY KEY    product id INT NOT NULL    quantity INT    min level INT    max level INT    CONSTRAINT fk inv product id     FOREIGN KEY  product id      REFERENCES products  product id      ON DELETE CASCADE      For this foreign key  we have specified the ON DELETE CASCADE clause which tells SQL Server to delete the corresponding records in the child table when the data in the parent table is deleted  So in this example  if a product id value is deleted from the products table  the corresponding records in the inventory table that use this product id will also be deleted

User · Answer

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

[sql-server] How do I use cascade delete with SQL Server?

Examples related to sql-server

Examples related to cascading-deletes