title: SQL no primary or candidate keys in the referenced table
body: <p>Here's my problem when I write my SQLSever expressions:</p>
  
  <blockquote>
    <p>There are no primary or candidate keys in the referenced table 'Room'<br>
    that match the referencing column list in the foreign key 'FK_<em>Booking</em>_RoomNo__4E298478'.</p>
  </blockquote>
  
  <p>And some snapshots to my program:</p>
  
  <pre><code>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));  &lt;---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));
  </code></pre>
  
  <p>and here is the Hotel table</p>
  
  <pre><code>CREATE TABLE Hotel(
     HotelNo  NVARCHAR(4)     not null,
     HotelName    NVARCHAR(25),
     City     NVARCHAR(25),
  
     PRIMARY KEY (HotelNo) );
  </code></pre>
  
  <p>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.</p>
  
  <p>Can someone please help me with this problem? </p>
  
  <p>Thank you in advance.</p>