How to delete rows in tables that contain foreign keys to other tables
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?
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.
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.
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
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
If you want to delete all the rows, you can use truncate with cascade:
TRUNCATE TABLE products CASCADE;
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
精彩评论