| Author |
Topic |
|
nunov
Starting Member
17 Posts |
 Posted - 2010-05-08 : 21:41:56
|
| Hi,I'm having a problem regarding some foreign key constraints.I have a table called 'user' with a primary key 'userid' and I have another table called 'friendship' where I insert the user friendships. The primary key of the table 'friendship' is 'userid' and 'friendid', and both atributes of the key are foreign keys of the table 'user'create table friendship( userid integer, friendid integer, primary key (userid, friendid), foreign key (userid) references user on delete cascade on update cascade, foreign key (friendid) references userI also created a trigger so there were no duplicates for a friendship.Example: Before I could have thisuser id friendid----------------------- 1 2 2 1With the trigger it is not possibleHowever I can't create a constrain in the foreign key (friendid) similar to the one that I created for foreign key (userid). Shouldn't it be possible after I created the trigger? Because I need to delete a friendship if a user is deleted, and I need to delete it either if he was registered in the user side or the friend side.Basically my question is: is anyway of adding this constrain to the foreign key friendid?NV |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-05-09 : 02:27:07
|
| You can't, for details refer to:[url]http://support.microsoft.com/kb/321843[/url][url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57797[/url] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-09 : 02:51:56
|
| what you can do is create a check constriant based on udf which checks if user,friend group is already present in table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nunov
Starting Member
17 Posts |
Posted - 2010-05-09 : 11:44:31
|
| So do you know anyway to make sure that when a user is deleted, all is friendships are too?NV |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-09 : 12:07:58
|
| Trigger?--Gail ShawSQL Server MVP |
|
|
|
nunov
Starting Member
17 Posts |
Posted - 2010-05-09 : 13:33:22
|
| I created a AFTER DELETE trigger on the table user, but when I deleted a user there was an error: "The DELETE statement conflicted with the REFERENCE constraint "FK__amigo__numutil__19A0ADA0"I didn't specify any constrain on this FK so the system must be assuming on delete no action :SNV |
|
|
|
nunov
Starting Member
17 Posts |
Posted - 2010-05-10 : 08:25:53
|
| Someone else knows a solution?NV |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-10 : 09:16:48
|
| If there's a foreign key, then make it cascading. If there isn't a foreign key, that's when you use a trigger.--Gail ShawSQL Server MVP |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-10 : 13:11:24
|
quote:
Originally posted by nunov
I created a AFTER DELETE trigger on the table user, but when I deleted a user there was an error: "The DELETE statement conflicted with the REFERENCE constraint "FK__amigo__numutil__19A0ADA0"I didn't specify any constrain on this FK so the system must be assuming on delete no action :SNV
shouldnt trigger be instead of? so as to remove all references of user in other table before removing it from user table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nunov
Starting Member
17 Posts |
Posted - 2010-05-10 : 14:20:39
|
| Thank you! It workedNV |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-10 : 14:24:47
|
great  welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Foreign Key Constrains