2 foreign keys point to the same primary key
in [Server Configuration]
|
From: 1_2_3_4_5_6 via SQLMonster.com on 29 Jun 2010 06:11 Hello all, i have question in my database i have table messages have 2 foreign keys (senderId, recieverId) both refrence to the same primary key userId in table users it gives me an error, it refuses to make the 2 relationships with the same column !!! what to do ?????????? HELP PLZ -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1
From: ben brugman on 29 Jun 2010 08:05 Hello, It is possible to have the 2 relationships as you describe. How do you make the relation ships ? Are the tables empty ? Is there data which would prevent making the relationship ? (All data present should be conform the relationship). Without the error it is difficult to see what is happening. So when do you get what error? You could sent us the complete example, or tell us what you do. If the database is not empty it could be that you have data which is not in accordance with the relationship. It is possible that you are creating the relationship (dragging and dropping in diagrams) in the 'wrong' direction. Good luck, Ben Brugman "1_2_3_4_5_6 via SQLMonster.com" <u61421(a)uwe> wrote in message news:aa3edb79750de(a)uwe... > Hello all, i have question in my database i have table messages have 2 > foreign keys (senderId, recieverId) both refrence to > > the same primary key userId in > > table users > > it gives me an error, it refuses to make the 2 relationships with the same > column !!! > > what to do ?????????? > > HELP PLZ > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1 >
From: Scott Morris on 29 Jun 2010 08:56 "1_2_3_4_5_6 via SQLMonster.com" <u61421(a)uwe> wrote in message news:aa3edb79750de(a)uwe... > Hello all, i have question in my database i have table messages have 2 > foreign keys (senderId, recieverId) both refrence to > > the same primary key userId in > > table users > > it gives me an error, it refuses to make the 2 relationships with the same > column !!! > > what to do ?????????? > > HELP PLZ Perhaps the problem is a poorly designed GUI? It is certainly possible to do this by writing the appropriate script create table users ( [user_id] int identity(1,1) not null , other_junk varchar(10) not null , constraint pk_users primary key ([user_id]) ); create table messages ( message_id int identity(1,1) not null , message_text varchar(100) not null , sender_id int not null , receiver_id int not null , constraint pk_messages primary key (message_id) , constraint fk_sender foreign key (sender_id) references users ([user_id]) , constraint fk_receiver foreign key (receiver_id) references users ([user_id]) ); go insert users (other_junk) select 'gomer' union all select 'goober'; insert messages (message_text, sender_id, receiver_id) values ('test message', 1, 2) select * from messages drop table messages; drop table users; go
From: Tom Cooper on 29 Jun 2010 11:39 When you are asking a question about an error message, you should tell us what the error message is. One possibility is that you are specifying ON DELETE CASCADE or ON UPDATE CASCADE in both of these foreign keys. If so, you will get an error message. SQL Server does not allow multiple foreign key relationships of the same two tables if they both have ON DELETE CASCADE or ON UPDATE CASCADE. Tom "1_2_3_4_5_6 via SQLMonster.com" <u61421(a)uwe> wrote in message news:aa3edb79750de(a)uwe... > Hello all, i have question in my database i have table messages have 2 > foreign keys (senderId, recieverId) both refrence to > > the same primary key userId in > > table users > > it gives me an error, it refuses to make the 2 relationships with the same > column !!! > > what to do ?????????? > > HELP PLZ > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1 >
|
Pages: 1 Prev: Moving SSIS 2005 packages from test to prod. Next: Is database available during a backup? |