64

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?

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • @pradeep: what database are you using? – Lieven Keersmaekers Sep 07 '10 at 06:07
  • 1
    database used by me: sql sever 2008 –  Sep 07 '10 at 06:10
  • i want simple Query i don't want to use store procedure –  Sep 07 '10 at 06:11
  • 1
    i dont think there is a simpler version than what Lieven has mentioned below. Just to clarify, the ON CASCADE DELETE option is not a part of the query, its a part of your Child table DDL statement. Refer http://www.mssqlcity.com/Articles/General/using_constraints.htm for an example – Jagmag Sep 07 '10 at 06:14

6 Answers6

33

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
26

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.

Community
  • 1
  • 1
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • 1
    In oracle, you will first need to delete the `dependencies` and then delete the parent. – Menelaos Kotsollaris Sep 30 '15 at 19:44
  • And what will happen if the referenced table is a main table for another tables and there are a FK with Delete rule: No Action. Does the server will allow to delete record from current MainTable where we set Delete rule to: Cascade? Thanks – Lev Z May 17 '17 at 15:34
  • @LevZ - No, it won't. You'll have to either remove the FK constraint or turn the child table's cascading deletes on. – Lieven Keersmaekers May 17 '17 at 16:27
12

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
Anil Soman
  • 2,443
  • 7
  • 40
  • 64
  • This is great however not always a solution when supporting low SQLite versions which may be the case for example in supporting Android API 4 which only ships with an SQLite just prior to them adding Foreign key support. – Daniel Jul 10 '12 at 14:07
5

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

Law
  • 359
  • 2
  • 12
Milena
  • 51
  • 2
0

If you want to delete all the rows, you can use truncate with cascade:

TRUNCATE TABLE products CASCADE;
div
  • 1,475
  • 3
  • 22
  • 32
-3

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

Deepu Surendran
  • 205
  • 2
  • 3