Two FK to one PK

Question

I'm using SQL Server Express 2012 and trying to make two relatonships, two FKs from the same table to one PK in another table.

The relationship seems to work because it shows up in the database diagram but when I try to save the changes, I receive the following error:

'Members' table saved successfully 'BookedResources' table - Unable to create relationship 'FK_BookedResourcesMemberId_MembersMemberId'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_BookedResourcesMemberId_MembersMemberId". The conflict occurred in database "resursBokning2", table "dbo.Members", column 'MemberId'.

MemberId in Members is the PK.

BookedResouce.EditedBy (FK) -> Member.MemberId (PK)
BookedResouce.MemberId (FK) -> Member.MemberId (PK)

Anybody know what this error is about? I've read that it should be OK to have this kind of relationship so it should work.

kinske · Answer 1 · 2013-10-15T09:24:13.977

0

The error occurs because maybe you use the same name for a foreign key twice, so change the name of the second to some other value, e.g.:

FK_BookedResourcesMemberId_MembersMemberId2

edited Oct 15 '13 at 09:24

answered Oct 15 '13 at 08:19

kinske

597
8
24

It could of course be that the member who originally created the record in the BookedResource table is stored in MemberId. In that case you have no duplicated data. – RFerwerda Oct 15 '13 at 08:30
Thats right, one member kan create, Another member can edit. If you look at what im Writing, one fk is called EditedBy, the other fk MemberId. – MikeAlike234 Oct 15 '13 at 08:40
@david2342 kinske is not refering to the name of the related columns, but to the name of the FK. your warning indicates both are called `FK_BookedResourcesMemberId_MembersMemberId` It might be that you accidently tried to add the same column relationship twice, you should probably double-check these. – Amber Oct 15 '13 at 08:54
Ok. The first one looks like ALTER TABLE [dbo].[BookedResources] WITH CHECK ADD CONSTRAINT [FK_BookedResourcesEditedBy_BookedResourcesMemberId] FOREIGN KEY([EditedBy]) REFERENCES [dbo].[Members] ([MemberId]) the other that im trying to add is called FK_BookedResourcesMemberId_MembersMemberId – MikeAlike234 Oct 15 '13 at 09:01
@david2342: OK. Now I understand what you're trying to do. Maybe you try it without using "WITH CHECK" – kinske Oct 15 '13 at 09:23

score 0 · Answer 2 · answered Oct 15 '13 at 08:22

From the error you've provided, it looks like you tried to name both foreign keys the same. As @kinse suggests, give each foreign key relationship a unique name. Also, consider whether you need two foreign keys to the same table - it could indicate that your database model is incomplete.

I am making an assumption that Members wouldn't be editing other members, so EditedBy (a member) and MemberId appear to be unnecessary on the members table.

Two FK to one PK

2 Answers2