TRiebel TRiebel - 1 month ago 15
SQL Question

Referencing FK from another table

Everything in theory looks right in my code, however I keep getting an error:


There are no primary or candidate keys in the referenced table 'GAMEPERIOD' that match the referencing column list in the foreign key 'PLAYS_FK_PERIOD'


Here is my code:

Table I'm trying to reference:

CREATE TABLE GAMEPERIOD(
GAMENUMBER VARCHAR(10),
PERIODNUMBER CHAR(1),
HSCORE VARCHAR(3),
LSCORE VARCHAR(3),
CONSTRAINT PERIOD_PK2 PRIMARY KEY (GAMENUMBER, PERIODNUMBER),
CONSTRAINT PERIOD_FK_GAME2 FOREIGN KEY (GAMENUMBER) REFERENCES GAME(GAMEID)
);


Table I'm trying to create:

CREATE TABLE PLAYS(
PERSONID CHAR(10) REFERENCES PLAYER,
GAMENUMBER VARCHAR(10),
PERIODNUMBER CHAR(1),
POINTS INTEGER,
MINUTESPLAYED NUMERIC(3,2),
FTA SMALLINT,
FTM SMALLINT,
FGA SMALLINT,
FGM SMALLINT,
TPA SMALLINT,
TPM SMALLINT,
TR SMALLINT,
OFFREB SMALLINT,
ASSISTS SMALLINT,
TURNOVERS SMALLINT,
STEALS SMALLINT,
BLOCKS SMALLINT,
PF SMALLINT,
CONSTRAINT PLAYS_PK PRIMARY KEY (PERSONID,GAMENUMBER,PERIODNUMBER),
CONSTRAINT PLAYS_POINTS CHECK (POINTS=(FTM+FGM*2+3*TPM)),
CONSTRAINT PLAYS_FK_PERIOD FOREIGN KEY (PERIODNUMBER, GAMENUMBER) REFERENCES GAMEPERIOD(PERIODNUMBER, GAMENUMBER)
);

Answer

You are mismatched on how you are accessing and defining your foreign keys. Your variables you need to flip flop (periodNumber, gameNumber), to (gameNumber, periodNumber)

Comments