Foreign Key Question from a NOOB
839331Feb 15 2011 — edited Feb 16 2011Hi Everyone - I am taking a relational database class and I am having some trouble with some homework. I am using Oracle 10G express and I am trying to create a FK constraint between two tables. I've managed to create a PK and 2 FK contraints OK, but I keep getting this error on the last one:
ORA-02270: no matching unique or primary key for this column-list
I am using the GUI, but here is the code for all four tables. ANY help would be appreciated. Thanks.
CREATE TABLE "HOTEL"
( "HOTELNO" CHAR(4) NOT NULL ENABLE,
"HOTELNAME" VARCHAR2(20) NOT NULL ENABLE,
"CITY" VARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "HOTEL_CON" PRIMARY KEY ("HOTELNO") ENABLE
)
/
CREATE TABLE "ROOM"
( "ROOMNO" VARCHAR2(4) NOT NULL ENABLE,
"HOTELNO" CHAR(4) NOT NULL ENABLE,
"TYPE" CHAR(1) NOT NULL ENABLE,
"PRICE" NUMBER(5,2) NOT NULL ENABLE,
CONSTRAINT "ROOM_CON" PRIMARY KEY ("ROOMNO", "HOTELNO") ENABLE,
CONSTRAINT "ROOM_CON_FK" FOREIGN KEY ("HOTELNO")
REFERENCES "HOTEL" ("HOTELNO") ENABLE
)
/
CREATE TABLE "GUEST"
( "GUESTNO" CHAR(4) NOT NULL ENABLE,
"GUESTNAME" VARCHAR2(20) NOT NULL ENABLE,
"GUESTADDRESS" VARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "GUEST_CON" PRIMARY KEY ("GUESTNO") ENABLE
)
/
CREATE TABLE "BOOKING"
( "HOTELNO" CHAR(4) NOT NULL ENABLE,
"GUESTNO" CHAR(4) NOT NULL ENABLE,
"DATEFROM" DATE NOT NULL ENABLE,
"DATETO" DATE NOT NULL ENABLE,
"ROOMNO" VARCHAR2(4) NOT NULL ENABLE,
CONSTRAINT "BOOKING_CON" PRIMARY KEY ("GUESTNO", "DATEFROM", "HOTELNO") ENABLE,
CONSTRAINT "BOOKING_CON_FK1" FOREIGN KEY ("HOTELNO")
REFERENCES "HOTEL" ("HOTELNO") ENABLE,
CONSTRAINT "BOOKING_CON_FK2" FOREIGN KEY ("GUESTNO")
REFERENCES "GUEST" ("GUESTNO") ENABLE
)
/