MS SQL two foreign keys problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi. i've got some problems during creating a user table and friend table.
AppUser table (general user info):
Id (int),
FirstName (varchar),
LastName (varchar)

and Friend table
Id (int),
AFriendId (int),
BFriendId (int)

and there are two foreign keys on AFriendId and BFriendId to AppUser.Id
i set cascade on update and delete for both.
the table is not saving, says that i must change on update and delete no no action. any ideas how to fix this problem?

Posted 21-Feb-15 7:44am

Arlert

Updated 21-Feb-15 8:42am

v2

Add a Solution

Comments

CHill60 21-Feb-15 13:54pm

Why would you want a cascade update?

Arlert 21-Feb-15 13:57pm

because if id will change, it must change on both tables

[no name] 21-Feb-15 14:46pm

"I must Change", no you do not have to. Read CHil60's comment! If you define it as cascaded you are out of pb.

CHill60 21-Feb-15 15:07pm

It's not a good idea to change id fields

Arlert 21-Feb-15 15:17pm

if i set no action on update, the problem is still unresolved

Kuthuparakkal 21-Feb-15 16:13pm

Bad design

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)

**Zoltán Zörgő** · Accepted Answer · 2015-02-21T13:09:00

On update cascade is just normal. If you change referred ID for any reason (why should you?) - the referring foreign key will be updated.
On delete cascade is also normal as when one person is deleted, all of hes/her friednship relations should be deleted too.
Well, what you desire is just normal.
Still, you can't assure with DDL, that the two foreign keys are not referring the same record in the other table. This is for some reason unacceptable for MSSQL Server - it complains about situations that can't appear.

Still, if you try the same thing in MySQL, PostgreSQL it will work without restriction. It is not your fault - your design is not wrong.

Add both foreign key constraints with "no action" and implement trigger(s) instead of trying to rely on DDL enforced action. Something like this:

SQL

CREATE TRIGGER AppUser_AD
ON dbo.AppUser
AFTER DELETE AS
BEGIN
 DELETE FROM dbo.Friend 
 WHERE 
	AFriendId in (SELECT id FROM DELETED) 
	OR 
	BFriendId in (SELECT id FROM DELETED) 
END
GO