MSSQL: Unable to create relationships for two foreign keys to the same table?

Question

Hi using SQL Server 2008,

I've built a small database for a baseball league, I'm having problem creating relationships between the Teams(PK: TeamID) and GameSchedule(PK: GameID, FK1: HomeTeamID, FK2: AwayTeamID)

I want to create relationships betwen the GameSchedule HomeTeamID, AwayTeamID to the Teams(TeamID)

Whenever I try to do this I get an error: (The TeamID is already the Primary Key in Teams)

'Teams' table saved successfully 'GameSchedule' table - Unable to create relationship 'FK_GameSchedule_Teams'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_GameSchedule_Teams". The conflict occurred in database "sll_2009", table "dbo.Teams", column 'TeamID'.

Sailplane

Have you specified the Deleting and Updating rules for those relations ? – Plugboard

You presumably have existing data that does not meet the constraint you are trying to add. – Downtrodden

More info: I created am empty playpen database, and I was able to create the two tables and all the relationships, the problem is that in the real database, I have existing records, and it won't let me create the relationship. – Sailplane

I did not specify Deleting or Updating rules for those relationships, I would have specified on the GameSchedule cascade delete. – Sailplane

Removing the On Delete and On Update rules cleared my error. – Raimondo

Answer 1

create table GameSchedule (
      GameID     integer not null
    , HomeTeamID integer not null
    , AwayTeamID integer not null
);

alter table GameSchedule
  add constraint pk_gsch  primary key (GameID)
, add constraint fk1_gsch foreign key (HomeTeamID) references Teams (TeamID)
, add constraint fk2_gsch foreign key (AwayTeamID) references Teams (TeamID)
;

Printable

Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk1_gsch". The conflict occurred in database "sll_2009", table "dbo.Teams", column 'TeamID'. – Sailplane

Well, I solved the problem, I had to uncheck the box: Check Existing Data on Creation: (set to 'NO') and it worked! Thanks. – Sailplane

@Eric, did not know you actually have some data in. The error means that you have some HomeTeamID values which do not exists in Teams table. – Printable

Excellent Damir; I did have some orphaned records! I removed them and all is well, thanks everyone for the help. I knew something was up when I created the empty tables and it worked fine. – Sailplane