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

User · Answer

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

User · Answer

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  id int     set  id   1     declare theMain cursor for select FK from MainTable where MainID    id     declare  fk Id int     open theMain     fetch next from theMain into  fk Id     while   fetch status 0     begin         --fkid is the foreign key          --Must delete from Main Table first then child          delete from MainTable where fkid    fk Id         delete from ReferencingTable where fkid    fk Id         fetch next from theMain into  fk Id     end     close theMain     deallocate theMain   hope is useful

User · Answer

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

User · Answer

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

User · Answer

Need to set the foreign key option as on delete cascade    in tables which contains foreign key columns     It need to set at the time of table creation or add later using ALTER table

[sql] How to delete rows in tables that contain foreign keys to other tables

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008