How can I delete rows in master,detail tables?

Question

0.00/5 (No votes)

See more:

Hi,

I've 4 tables that suppose: t1.id is PK and t2.id,t3.id,t4.id are FK,now,
how can I delete a specific row(id) of all tables?
of course, I can do it with 4 DeleteCommands in a storedProcedure, but I think it's not optimized,is there better solution?

Posted 28-Nov-11 22:33pm

mirshakkeh

Add a Solution

3 solutions

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)

Dylan Morley · Answer 1 · 2011-11-28T22:49:00

4 delete commands wrapped up in a transaction within a stored procedure is absolutely fine, you'd have to delete from the 'bottom up' though.

e.g

SQL

BEGIN TRANS

    DELETE FROM Table4 WHERE Table1_PK = 1

    DELETE FROM Table3 WHERE Table1_PK = 1

COMMIT TRANS

There's nothing wrong with that at all.

You could also enforce cascade deletes on your primary key (Table1) so that we you delete from this table, it automatically deletes all related data

It's up to you how to implement, personally I'm not massively keen on cascade deletes. I prefer to have to explicitly tell a database what to delete and get errors if something is accidentally deleted :)

Eduard Lu · Answer 2 · 2011-11-28T22:53:00

Solution 2

Try the ff codes:

SQL

DELETE FROM t1
FROM t2
FROM t3
FROM t4
JOIN t2 ON t2.id = t1.id
JOIN t3 ON t3.id = t1.id
JOIN t4 ON t4.id = t1.id
WHERE id = @ID

I found this on the net. Here for your reference:
http://msdn.microsoft.com/en-us/library/ms189835.aspx[^]

Regards,
Eduard

Posted 28-Nov-11 22:53pm

Eduard Lu

Amir Mahfoozi · Answer 3 · 2011-11-28T23:01:00

You can apply cascaded delete to their relationship.

Open SSMS then go to design view of t1 then open Relationships window and set the Delete Rule of all relation to Cascade (in Insert and Update Specification section)

then save the changes.

From now on if you delete a record in t1 all the related rows in t2 , t3, t4 will be deleted automatically.

Hope it helps.

How can I delete rows in master,detail tables?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0