Rob Bor Rob Bor - 2 months ago 13
SQL Question

Error when trying to create foreign key for table in SQL?

I am creating a Screen Table:

CREATE TABLE SCREEN(
BRANCHID CHAR(2) NOT NULL,
SCREENID CHAR(2) NOT NULL,
SCREENCACPACITY NUMBER(3),
CONSTRAINT SCREEN_PK PRIMARY KEY(BRANCHID, SCREENID),
CONSTRAINT SCREEN_FK FOREIGN KEY(BRANCHID) REFERENCES BRANCH(BRANCHID));


But when I create a Sessions Table:

CREATE TABLE SESSIONS(
SESSIONID CHAR(4) NOT NULL,
BRANCHID CHAR(2) NOT NULL,
SCREENID CHAR(2) NOT NULL,
MOVIEID CHAR(2) NOT NULL,
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT SESSIONS_PK PRIMARY KEY(SESSIONID),
CONSTRAINT SESSIONS_FK1 FOREIGN KEY(BRANCHID) REFERENCES BRANCH(BRANCHID),
CONSTRAINT SESSIONS_FK2 FOREIGN KEY(SCREENID) REFERENCES SCREEN(SCREENID),
CONSTRAINT SESSIONS_FK3 FOREIGN KEY(MOVIEID) REFERENCES MOVIE(MOVIEID));


I get an "no matching unique or primary key for this column-list" error. I know the error is with the Screen table, as I have tried all Foreign Keys on their own, and Screen was the only one that gave me the error. So, when I try to run the complete script, it doesnt work because of Screen.

Branch and Movie Screen have been made and are more or less the same as Screen. Help pl0x

Answer

Most (if not all) of the SQL flavors that support foreign keys will force you to point the foreign key to the whole primary key. In your case, table SCREEN has (BRANCHID, SCREENID) AS PK, but your SESSIONS_FK2 FK points to SCREENID only.

You should create a foreign key that specifies both columns. Something like:

CONSTRAINT FK_SESSION_SCREEN FOREIGN KEY (BRANCHID, SCREENID) REFERENCES SCREEN(BRANCHID, SCREENID)

Other things to consider:

1) If you aim for normalization, maybe branch and screen should be separated (thus, having a single column primary key for each)

2) Name your constraints with meaningful names, so that you directly understand what they do.