I\'m trying to create fk, but I\'m getting the error: Msg 1776, Level 16, State
ID: 3865875 • Letter: I
Question
I'm trying to create fk, but I'm getting the error: Msg 1776, Level 16, State 0, Line 159
There are no primary or candidate keys in the referenced table 'TDonorPlayers' that match the referencing column list in the foreign key 'TDonations_TDonorPlayers_FK'.
Msg 1750, Level 16, State 0, Line 159
Could not create constraint or index. See previous errors.
CREATE TABLE TTeams
(
intTeamID INTEGER NOT NULL
,intSportID INTEGER NOT NULL
,intTeamLevelID INTEGER NOT NULL
,intTeamGenderID INTEGER NOT NULL
,strTeamName VARCHAR(50) NOT NULL
,CONSTRAINT TTeams_PK PRIMARY KEY ( intTeamID )
)
CREATE TABLE TSports
(
intSportID INTEGER NOT NULL
,strSportName VARCHAR(50) NOT NULL
,CONSTRAINT TSports_PK PRIMARY KEY ( intSportID )
)
CREATE TABLE TPlayers
(
intPlayerID INTEGER NOT NULL
,intTeamID INTEGER NOT NULL
,intShirtSizeID INTEGER NOT NULL
,strFirstName VARCHAR(50) NOT NULL
,strLastName VARCHAR(50) NOT NULL
,strEmail VARCHAR(50) NOT NULL
,intDonorID INTEGER NOT NULL
,CONSTRAINT TPlayers_PK PRIMARY KEY ( intPlayerID )
)
CREATE TABLE TDonors
(
intDonorID INTEGER NOT NULL
,strFirstName VARCHAR(50) NOT NULL
,strLastName VARCHAR(50) NOT NULL
,strEmail VARCHAR(50) NOT NULL
,CONSTRAINT TDonors_PK PRIMARY KEY ( intDonorID )
)
CREATE TABLE TDonorPlayers
(
intDonorPlayerID INTEGER NOT NULL
,intDonationID INTEGER NOT NULL
,intPlayerID INTEGER NOT NULL
,intDonorID INTEGER NOT NULL
,dtmPlayDate DATETIME NOT NULL
,CONSTRAINT TDonorPlayers_PK PRIMARY KEY ( intDonorPlayerID)
)
CREATE TABLE TDonations
(
intDonationID INTEGER NOT NULL
,intDonorPlayerID INTEGER NOT NULL
,monPledgeRecieved Money NOT NULL
,intPaymentMethodID INTEGER NOT NULL
,monPricePerHole Money NOT NULL
,dtmDonationDate DATETIME NOT NULL
,CONSTRAINT TDonations_PK PRIMARY KEY ( intDonationID,intDonorPlayerID )
)
CREATE TABLE TPaymentMethod
(
intPaymentMethodID INTEGER NOT NULL
,strPaymentType VARCHAR(500) NOT NULL
,CONSTRAINT TPaymentMethod_PK PRIMARY KEY ( intPaymentMethodID )
)
CREATE TABLE TTeamLevel
(
intTeamLevelID INTEGER NOT NULL
,strTeamLevel VARCHAR(500) NOT NULL
,CONSTRAINT TTeamLevel_PK PRIMARY KEY ( intTeamLevelID )
)
CREATE TABLE TTeamGender
(
intTeamGenderID INTEGER NOT NULL
,strTeamGender VARCHAR(500) NOT NULL
,CONSTRAINT TTeamGender_PK PRIMARY KEY ( intTeamGenderID )
)
CREATE TABLE TShirtSize
(
intShirtSizeID INTEGER NOT NULL
,strShirtSize VARCHAR(500) NOT NULL
,CONSTRAINT TShirtSize_PK PRIMARY KEY ( intShirtSizeID )
)
-- --------------------------------------------------------------------------------------------------------------------------
-- Step #1.2: Identify and Create Foreign Keys
-- --------------------------------------------------------------------------------------------------------------------------
-- # Child Parent Column(s)
-- ------ ------- ---------
--1 TTeams TSports intSportID
--2 TTeams TTeamLevel intTeamLevelID
--3 TTeams TTeamGender intTeamGenderID
--4 TDonations TDonorPlayers intDonationID
--1
ALTER TABLE TTeams ADD CONSTRAINT TTeams_TSports_FK
FOREIGN KEY (intSportID) REFERENCES TSports (intSportID)
--1
ALTER TABLE TTeams ADD CONSTRAINT TTeams_TTeamLevel_FK
FOREIGN KEY (intTeamLevelID) REFERENCES TTeamLevel (intTeamLevelID)
--1
ALTER TABLE TTeams ADD CONSTRAINT TTeams_TTeamGender_FK
FOREIGN KEY (intTeamGenderID) REFERENCES TTeamGender (intTeamGenderID)
--2
ALTER TABLE TDonations ADD CONSTRAINT TDonations_TDonorPlayers_FK
FOREIGN KEY ( intDonationID,intDonorPlayerID) REFERENCES TDonorPlayers (intDonationID,intDonorPlayerID)
Explanation / Answer
The syntax is not correct Use following query
ALTER TABLE TDonations ADD CONSTRAINT TDonations_TDonorPlayers_FK
FOREIGN KEY (intDonorPlayerID) REFERENCES TDonorPlayers (intDonorPlayerID)