SQL & PL/SQL

Hi all,

First post and Oracle newbie so go easy on me. Done research on this and I'm not sure what I'm doing wrong.

I'm creating a database with 10 tables- there's a table that is having issues, the "faculty" table. The error is ORA-02270: no matching unique or primary key for this column-list. On research, it appears that I have not created a primary key that the foreign key is trying to reference. But reviewing my code, I struggle to see the issue.

The "location" table, which contains the composite primary key, creates fine:

CREATE TABLE location
(
Room_ID VARCHAR2(5) NOT NULL,
Room_type VARCHAR2(4) NOT NULL,
Building VARCHAR2(12) NOT NULL,
RoomNo VARCHAR2(5) NOT NULL,
Capacity VARCHAR2(5) NOT NULL,
PRIMARY KEY, (Room_ID, Room_type)
);

the error appears when creating the "faculty" table:

CREATE TABLE faculty
(
Faculty_ID VARCHAR2(10) PRIMARY KEY,
Faculty_name VARCHAR2(15) NOT NULL,
Faculty_phone VARCHAR2(11) NOT NULL,
Room_ID VARCHAR2(5) NOT NULL REFERENCES location (Room_ID),
Room_type VARCHAR2(4) NOT NULL REFERENCES room (Room_type),
);

The error is for the line Room_ID VARCHAR2(5) NOT NULL REFERENCES location (Room_ID). If I remove this, the table creates. I tried modifying the faculty table creation syntax to

CREATE TABLE faculty
(
Faculty_ID VARCHAR2(10) PRIMARY KEY,
Faculty_name VARCHAR2(15) NOT NULL,
Faculty_phone VARCHAR2(11) NOT NULL,
Room_ID VARCHAR2(5) NOT NULL REFERENCES location (Room_ID, Room_type)
);

But instead I got the error ORA-02256: number of referencing columns must match referenced columns.

I am guessing the underlying issue is with the way my foreign keys are referencing my composite primary key of two columns. Can anyone help me out? I understand the problem, I'm guessing there's a syntax error.

Regards