CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
jerle78
jerle78

Reputation: 157

How to Delete data from a table which contain self referencing foreign key

This is for a MS SQL 2005 server. I have a query that is deleting all orphaned records in a table. However, this table has self-referencing FKs. I need to delete these as well, but am unclear how to do so. The current script deletes all records that not appear as FKs in other tables, but i didn't take into account the self-referencing FKs in its own table. the table is simple:

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

Upvotes: 5

Views: 2909

Answers (4)

Aaron Silverman
Aaron Silverman

Reputation: 22645

If you are going to have multiple levels, you can use a common table expression to get the IDs you need to delete 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

Upvotes: 3

user3535873
user3535873

Reputation: 1

You are trying to removing records while maintaining self referential integrity. I would advise

  1. drop the constraint
  2. then write the query to delete records which are related using join.

Create a trigger (for delete) 

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

Upvotes: 0

abatishchev
abatishchev

Reputation: 100278

Probably you can configure your constraint to be deleted on parent item deletion:

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

Upvotes: 0

Russ
Russ

Reputation: 4163

It's probably simplest to do this in two steps, first delete the orphaned records, then to delete the referenced children now orphaned after the first delete:

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)

Upvotes: 0

Related Questions