CODEWITHSUNDEEP
sql-serverentity-relationshipforeign-key-relationship
Eric
Eric

Reputation: 472

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

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'.

Upvotes: 6

Views: 11645

Answers (1)

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

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)
;

Upvotes: 6

Related Questions