Error adding Foreign Key Constraint

Question

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

I would suggest that your team primary key is problematic. Why can't you have two teams in a given location with the same name? They might be in different divisions. Also, copying the team all over the place is problematic, what happens when the team name changes? In general you seem to be lacking of necessary table for proper normalization. I would think you need tables for conference, division, stadium, state at the very least. Otherwise you just keep duplicating information. Also, varchar(50) for everything seems off. Why not state being char(2) for example. — Sean Lange
Your ALTER TABLE statement should work based on the images you have shown, which makes me think they are not currently accurate. Refresh your SSMS view of the tables, and verify they haven't changed. If that is not the case, see if you can script out the CREATE TABLE statements and post them so that we can try to reproduce this issue. I don't see how it can possibly be occurring if your question is accurate. — Tab Alleman
One thing you might try, first, though, is put a space after "TEAM" before the open parenthesis, and precede it with "dbo." just to see if that makes a difference. It shouldn't, but I'm a stickler for well-formatted SQL. — Tab Alleman
I have refreshed and tried with the space and adding "DBO." to player and team. No dice. — Xantom

pmbAustin pmbAustin · Accepted Answer · 2015-07-17T17:12:03

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.

Error adding Foreign Key Constraint

2 Answers