How to delete data from a table containing a foreign key of a foreign link

Question

This is for MS SQL 2005 server. I have a query that deletes all lost records in a table. However, there are self-regulating FKs in this table. I also need to remove them, but it is not clear how to do this. The current script deletes all records that do not appear as FKs in other tables, but I did not take into account self-regulating FKs in my table. the table is simple:

PK, FK, DAta 1, NULL, jibberjab 2, 1, jibberjab2 3, 1, skdfasfa 

Answer 1

If you intend to have several levels, you can use a common table expression to get the identifiers that need to be removed and take care of the orphans and all their descendants in one statement:

  WITH cte AS ( SELECT pk FROM myTable WHERE id = 1 --pk to delete UNION ALL SELECT t.pk FROM myTable t JOIN cte c ON t.fk = c.pk ) DELETE t FROM cte c JOIN myTable t ON c.pk = t.pk 

Answer 2

This is probably the easiest way to do this in two steps: first delete the orphaned entries, and then delete the child links that are now orphaned after the first deletion:

 DELETE FROM TABLE1 WHERE NOT EXISTS (SELECT 1 FROM TABLE2 WHERE TABLE2.FK = TABLE1.PK) DELETE FROM TABLE1 WHERE NOT EXISTS (SELECT 1 FROM TABLE1 B WHERE B.FK = TABLE1.PK) 

Answer 3

You can probably set your restriction on deletion when deleting the parent:

 ALTER TABLE table1 WITH CHECK ADD CONSTRAINT FK_table1_table2 FOREIGN KEY(column1) REFERENCES table2 (ID) ON DELETE SET NULL -- here ALTER TABLE table1 CHECK CONSTRAINT FK_table1_table2 GO 

Answer 4

You are trying to delete records while maintaining self-referential integrity. I would recommend

• remove restriction
• then write a query to delete entries related to use join.

Create a trigger (for deletion)

 CREATE TRIGGER ON ExTable FOR DELETE AS IF EXISTS (SELECT * FROM ExTable AS tbl JOIN DELETED AS del ON del.FK=tbl1.PK) DELETE FROM ExTable FROM ExTable ex JOIN DELETED del ON ex.FK=del.ID