Click here to Skip to main content
15,443,631 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:

I am trying to delete record from primary key table which having foreign key reference.i want delete record first from foreign key table then primary key.i know by on delete cascade it is possible.but i am want to apply this on delete cascade on existing table without altering table.I known by droping foregn key ref and redefining with on delete cascade it is possible but i want other option .is there any sql query by which i can apply on delete cascade within query it self.

1 solution

Your best bet is the one you already rejected: delete cascading. That ensures that the table that has the foreign key is always up to date with the primary table. However, if you cannot modify this table, your next best option would be to add a trigger. This is a different type of modification and it might be something that you could do. You would do an Instead Of trigger on the DELETE command. Before the delete, you would then delete all of the records from the foreign key table that correspond to the row being deleted.

If neither of these options are acceptable and you must do it in your query, you are going to need to do a SELECT on the rows you want to delete and then delete the items in the foreign key table that match. Once that is done, you can finish with the actual delete of the primary key table. It would look something like this (pseudo-code):

FROM ForeignKeyTable
WHERE LinkingID IN (SELECT * FROM PrimaryKeyTable WHERE <criteria to="" find="" rows="" be="" deleted="">);

FROM PrimaryKeyTable
WHERE <criteria to="" find="" rows="" be="" deleted=""></criteria></criteria>

Those two statements, working together, will delete all of the rows that reference the primary key table and then they will delete the rows in the primary key table as well.
Share this answer
Maciej Los 20-Jun-12 7:57am     CRLF
On MS SQL Server 2005 Express Edition the statement: DELETE * FROM ForeignKeyTable WHERE ... produce an error. Please, remove * from your query. ;)
Tim Corey 20-Jun-12 8:18am    
Wow, too early in the morning I guess. Good catch. Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900