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

Question

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

Where was the `BRANCHES` table created, relative to the two `CREATE TABLE` statements you showed us above? — Tim Biegeleisen, Sep 27 '16 at 05:27

score 2 · Accepted Answer · answered Sep 27 '16 at 05:30

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.

This seemed to work, thanks. Ill keep those tips in mind, thanks. — Alonzo Robbe, Sep 27 '16 at 05:44

score 1 · Answer 2 · answered Sep 27 '16 at 05:30

1

Hi Rob,<> Here in the Session table you have defined foreign key, but in the parent table .i.e, in screen table there is no concern primary key. Kindly check the other tables, of it has primary key constraint or not.

answered Sep 27 '16 at 05:30

Jim Macaulay

4,709
4
28
53

score 1 · Answer 3 · answered Sep 27 '16 at 05:34

1

The fact that SCREENID is not UNIQUE or PRIMARY KEY can not allow you to make it a FOREIGN KEY as you mention in CONSTRAINT SESSIONS_FK2 FOREIGN KEY(SCREENID).

So you need to make SCREENID as PRIMARY KEY of the table SCREEN.

answered Sep 27 '16 at 05:34

P.Bra

264
1
12

I have, its a composite with BRANCHID, or is that not what you mean? – Alonzo Robbe Sep 27 '16 at 05:37

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

3 Answers3