How to delete a row in a table which has a column as a foreign key to other tables in SQL Server?

Question

0.00/5 (No votes)

See more:

I have a master table with a primary key column refered to by many columns in other tables in the same database, I want to delete a row in that table without having to delete others first (which have referent values to that table). I can simply do this in Access but it seems a bit tricky in SQL Server.
Could you please guide me through the how to achieve that? I like the simplicity, I felt how it is in Access and at that time I thought that's why they provide the so-called "foreign key"!
Thank you very much!

Posted 21-Oct-11 16:55pm

King Boy

Updated 21-Oct-11 17:50pm

v5

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)

Not Active · Accepted Answer · 2011-10-21T18:10:00

Solution 1

The whole point in making it difficult is because of referential entegrity. If you delete the row from the parent table then all rows in any other table would be ophaned and would cause problems with queries and such. Set ON DELETE CASCADE on the relationship to delete the rows in the child table as well.

http://msdn.microsoft.com/en-us/library/aa933119(v=sql.80).aspx[^]

Posted 21-Oct-11 18:10pm

Not Active

Comments

Mehdi Gholam 22-Oct-11 0:26am

It's like saying can I open a safe without the key! 5!

[no name] 22-Oct-11 2:39am

Thank you very much!
You have helped me out!

sandeep12jain · Accepted Answer · 2011-10-22T01:28:00

Solution 2

SQL

ALTER TABLE MasterTable
ADD CONSTRAINT fk_xyz 
FOREIGN KEY (xyz) 
REFERENCES ChildTable (xyz) ON DELETE CASCADE

Posted 22-Oct-11 1:28am

sandeep12jain

Comments

[no name] 23-Oct-11 14:05pm

Thank you very much!

Sudhir Kumar Srivastava lko · Accepted Answer · 2011-10-22T02:24:00

Solution 3

First u have to delete the record from Child table then remove from parent table .

Posted 22-Oct-11 2:24am

Sudhir Kumar Srivastava lko

Comments

[no name] 23-Oct-11 14:13pm

Oh that's the normal way. I think you should refer to other solutions for this question of mine and feel how easy to delete the rows. I don't know what the advantage of doing that the normal way (as you suggested) is (compared to the way I was asking for), but of course it is also a way to do in some case.
Thank you!