Don't understand why my SQL isn't working

Question

/* DROP TABLE STATEMENTS*/
DROP TABLE BUILDING CASCADE CONSTRAINTS PURGE;
DROP TABLE ROOM CASCADE CONSTRAINTS PURGE;
DROP TABLE CONFERENCESESSION CASCADE CONSTRAINTS PURGE;
DROP TABLE SPEAKER CASCADE CONSTRAINTS PURGE;

/* CREATE TABLE STATEMENTS*/
CREATE TABLE BUILDING(
BUILDINGNO CHAR(2),
BUILDINGWING VARCHAR2(15),
BUILDINGLANE VARCHAR2(15),
CONSTRAINT BUILDING_PK PRIMARY KEY(BUILDINGNO));

CREATE TABLE ROOM(
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
ROOMCAPACITY NUMBER(3),
CONSTRAINT ROOM_PK PRIMARY KEY(BUILDINGNO, ROOMNO),
CONSTRAINT ROOM_FK FOREIGN KEY(BUILDINGNO) REFERENCES BUILDING(BUILDINGNO));

CREATE TABLE SPEAKER(
SPEAKERID CHAR(2),
SPEAKERNAME VARCHAR2(20),
SPEAKERADDRESS VARCHAR2(50),
SPEAKERPHONE CHAR(12),
CONSTRAINT SPEAKER_PK PRIMARY KEY(SPEAKERID));

CREATE TABLE CONFERENCESESSION(
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4, 2),
CONSTRAINT CONFERENCE_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCE_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCE_FK3 FOREIGN KEY(SPEAKERID) REFERENCES SPEAKER(SPEAKERID));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I get this error when running the above code:

Table BUILDING dropped.

Table ROOM dropped.

Error starting at line : 4 in command -
DROP TABLE CONFERENCESESSION CASCADE CONSTRAINTS PURGE
Error report -
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:

Table SPEAKER dropped.

Table BUILDING created.

Table ROOM created.

Table SPEAKER created.

Error starting at line : 28 in command -
CREATE TABLE CONFERENCESESSION(
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4, 2),
CONSTRAINT CONFERENCE_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCE_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCE_FK3 FOREIGN KEY(SPEAKERID) REFERENCES SPEAKER(SPEAKERID))

Error report -
ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

Any help?

HoneyBadger HoneyBadger · Accepted Answer · 2017-10-02T09:40:55

CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO): A foreign key has to reference the entire primary key (or unique key) of another table. So simply add buildingno:

CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(BUILDINGNO, ROOMNO) REFERENCES ROOM(BUILDINGNO, ROOMNO)

Don't understand why my SQL isn't working

1 Answers