on delete cascade for already created table

Question

1.00/5 (1 vote)

See more:

hi...........

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.
Thanx

Posted 19-Jun-12 23:52pm

Vijay Walunj,Navi mumbai

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tim Corey · Answer 1 · 2012-06-20T01:11:00

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):

SQL

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

DELETE 
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.