How to delete rows in tables that contain foreign keys to other tables

Question

Suppose there is a main table containing a primary key and there is another table which contains a foreign key to this main table. So if we delete the row of main table it will delete the child table also.

How do I write this query?

Answer 1

From your question, I think it is safe to assume you have CASCADING DELETES turned on.
All that is needed in that case is

DELETE FROM MainTable
WHERE PrimaryKey = ???

You database engine will take care of deleting the corresponding referencing records.

Answer 2

You can alter a foreign key constraint with delete cascade option as shown below. This will delete chind table rows related to master table rows when deleted.

ALTER TABLE MasterTable
ADD CONSTRAINT fk_xyz 
FOREIGN KEY (xyz) 
REFERENCES ChildTable (xyz) ON DELETE CASCADE 

Answer 3

First, as a one-time data-scrubbing exercise, delete the orphaned rows e.g.

DELETE 
  FROM ReferencingTable 
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM MainTable AS T1
                    WHERE T1.pk_col_1 = ReferencingTable.pk_col_1
                  );

Second, as a one-time schema-alteration exercise, add the ON DELETE CASCADE referential action to the foreign key on the referencing table e.g.

ALTER TABLE ReferencingTable DROP 
   CONSTRAINT fk__ReferencingTable__MainTable;

ALTER TABLE ReferencingTable ADD 
   CONSTRAINT fk__ReferencingTable__MainTable 
      FOREIGN KEY (pk_col_1)
      REFERENCES MainTable (pk_col_1)
      ON DELETE CASCADE;

Then, forevermore, rows in the referencing tables will automatically be deleted when their referenced row is deleted.

Answer 4

If you have multiply rows to delete and you don't want to alter the structure of your tables you can use cursor. 1-You first need to select rows to delete(in a cursor) 2-Then for each row in the cursor you delete the referencing rows and after that delete the row him self.

Ex:

--id is primary key of MainTable

declare theMain cursor for select id from MainTable declare @mainId uniqueidentifier open theMain fetch next from theMain into @mainId while @@fetch_status=0 begin

    --smth_id is the foreign key 

delete from ReferencingTable where smth_id=@mainId

delete from MainTable where id=@mainId
fetch next from theMain into @mainId

end close theMain deallocate theMain

hope is useful