D. D D. D - 2 months ago 18
SQL Question

SQL Error: ORA-02270: Error when making a table

These are the tables that I have so far, and the error is for the table SESSIONS and I can't seem to find what the error is? Did I enter the primary keys and foreign keys in wrong?

CREATE TABLE BRANCH(
BRANCHID CHAR(2) NOT NULL,
BRANCHNAME VARCHAR2(20),
BRANCHSUBURB VARCHAR2(15),
BRANCHPOSTCODE CHAR(4),
CONSTRAINT BRANCH_PK PRIMARY KEY(BRANCHID));

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

CREATE TABLE MOVIE(
MOVIEID CHAR(2) NOT NULL,
MOVIENAME VARCHAR2(20),
MOVIEGENRE VARCHAR2(20),
MOVIECLASSIFICATION VARCHAR2(5),
CONSTRAINT MOVIE_PK PRIMARY KEY(MOVIEID));

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, BRANCHID, SCREENID, MOVIEID),
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));

Answer

You have a composite primary key for SCREEN. Hence the problem is this:

CONSTRAINT SESSIONS_FK2 FOREIGN KEY(SCREENID) REFERENCES SCREEN(SCREENID),

You can easily fix this because you also have BRANCHID:

CONSTRAINT SESSIONS_FK2 FOREIGN KEY(SCREENID, BRANCHID) REFERENCES SCREEN(SCREENID, BRANCHID),
Comments