Can't create table relationship - SQL Server 2005

Question

I am getting an error when trying to create a relationship within two tables in Sql Server 2005. I am attempting to create the relationships using the database diagram feature. I have a Player table and a Message table. I want to create two relationships from the Message table to the Player table. I can successfully create the first relationship from Message.From to Player.PlayerId. When I attempt to create the second relationship from Message.To to Player.PlayerId I get an error that says...

'Player' table saved successfully 'Message' table - Unable to create relationship 'FK_Message_Player_To'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Message_Player_To". The conflict occurred in database "TPF", table "dbo.Player", column 'PlayerId'.

Message
===================
MessageId INT PK,
From INT, -- refers to Player PK
To INT -- also refers to Player PK

Player
=================
PlayerId INT PK,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)

I would like to know what the most optimal way of solving this issue is. Any explanation is greatly appreciated. Thanks in advanced for your help.

Answer 1

Refresh your diagram, and check the names of what you have. It sounds like it's trying to make (or is has made) the FK on the [From] field using the name FK_Message_Player_To, giving you a name conflict.

Alternatively, do it using T-SQL instead of the GUI.

Answer 2

This error occurs if you have data in the table that conflicts with the key you are trying to create.

Either delete the offending data in the "To" field, or change the "Check Existing Data on Creation or Re-enabling" option to "No".