CODEWITHSUNDEEP
sql-serverforeign-keys
Xantom
Xantom

Reputation: 67

Error adding Foreign Key Constraint

I am trying to add a Foreign Key Constraint but can't figure out what I am doing wrong. Thanks in advance for any suggestions.

USE MyTestDB
ALTER TABLE Player
ADD CONSTRAINT FK_Player_Team
FOREIGN KEY (Team_Name,Team_Location)
REFERENCES TEAM(Team_Name,Team_Location)

Msg 1776, Level 16, State 0, Line 8 There are no primary or candidate keys in the referenced table 'TEAM' that match the referencing column list in the foreign key 'FK_Player_Team'. Msg 1750, Level 16, State 0, Line 8 Could not create constraint. See previous errors.

enter image description here

enter image description here

Upvotes: 1

Views: 267

Answers (2)

Xantom
Xantom

Reputation: 67

Changed the order of the column names, worked fine. Can't believe I didn't see that before!

USE MyTestDB
ALTER TABLE dbo.Player
ADD CONSTRAINT FK_Player_Team
FOREIGN KEY (Team_Location,Team_Name)
REFERENCES dbo.TEAM (Team_Location,Team_Name)

Upvotes: 1

pmbAustin
pmbAustin

Reputation: 3970

The error message is actually pretty clear in describing exactly what the problem is.

You're trying to add a foreign key that says "For every Team Name and Location in the Player table, there must be a matching Team Name and Location in the Team table".

The error message is explicitly telling you it can't add the constraint because this is not the case: there is a Team Name and Location in the Player table that DOES NOT exist in the Team table.

A query like this should list out the rows that are the problem... and you'll need to add rows with these Team Names and Locations to your Team table (or delete these rows from the Player table) before you can successfully add the constraint:

SELECT *
  FROM dbo.Player p
 WHERE NOT EXISTS (SELECT * 
                     FROM dbo.Team t 
                    WHERE t.Team_Name = p.Team_Name AND t.Team_Location = p.Team_Location)

Additionally, a Foreign key reference must reference a primary key index. Since the primary key in this case is on the first and last names, rather than the team name and location, it can't apply the FK constraint. It needs to do this so it can do efficient look-ups and have uniqueness guarantees in order to validate the constraint.

Upvotes: 1

Related Questions