Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Foreign Key Question from a NOOB

839331Feb 15 2011 — edited Feb 16 2011
Hi 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
)
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 16 2011
Added on Feb 15 2011
21 comments
425 views