CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Allan Jiang
Allan Jiang

Reputation: 11331

SQL no primary or candidate keys in the referenced table

Here's my problem when I write my SQLSever expressions:

There are no primary or candidate keys in the referenced table 'Room'
that match the referencing column list in the foreign key 'FK_Booking_RoomNo__4E298478'.

And some snapshots to my program:

CREATE TABLE Booking(
   HotelNo  NVARCHAR(4)     not null,
   GuestNo      SMALLINT,  
   DateFrom DATETIME    not null,
   DateTo       DATETIME,
   RoomNo       SMALLINT    not null,

   PRIMARY KEY (HotelNo,DateFrom,RoomNo), 
   FOREIGN KEY (HotelNo) REFERENCES Hotel(HotelNo),
   FOREIGN KEY (GuestNo) REFERENCES Guest(GuestNo),
   FOREIGN KEY (RoomNo) REFERENCES Room(RoomNo));  <---trouble on this line

CREATE TABLE Room(
   RoomNo       SMALLINT    not null, 
   HotelNo  NVARCHAR(4)     not null, 
   RoomType     NVARCHAR(25),
   Price        DECIMAL(5,2),

   PRIMARY KEY (RoomNo,HotelNo), 
   CONSTRAINT fk_Room FOREIGN KEY (HotelNo) REFERENCES Hotel(HotelNo));

and here is the Hotel table

CREATE TABLE Hotel(
   HotelNo  NVARCHAR(4)     not null,
   HotelName    NVARCHAR(25),
   City     NVARCHAR(25),

   PRIMARY KEY (HotelNo) );

I tried to do some search on this problem, and it says this might be caused when there's no primary key defined in the table Room, but as above, it is defined.

Can someone please help me with this problem?

Thank you in advance.

Upvotes: 2

Views: 3210

Answers (1)

Anri
Anri

Reputation: 6265

Any reference to composite key must also include both columns.

FOREIGN KEY (RoomNo, HotelNo) REFERENCES Room(RoomNo, HotelNo))

Upvotes: 3

Related Questions