there are not Primary or candidate keys in the referenced table 'hotel' that match the referencing column list in the foreign key 'fk_rooms_hotel'

Question

Hello there could anyone help me with this error. I am just learning SQL and have no idea how to fix it. It says that there are not Primary or candidate keys in the referenced table 'hotel' that match the referencing column list in the foreign key 'fk_rooms_hotel'.

CREATE database Hotel_database;
USE Hotel_database; 

-- Table `addresses`

CREATE TABLE addresses (
  address_id INT PRIMARY KEY NOT NULL ,
  address_line1 VARCHAR(100) NULL,
  address_line2 VARCHAR(100) NULL,
  city VARCHAR(45) NULL,
  state VARCHAR(45) NULL,
  country VARCHAR(45) NULL,
  zipcode VARCHAR(8) NULL,
  );

  -- -----------------------------------------------------
-- Table `hotel_database`.`hotel_chain`
-- -----------------------------------------------------

CREATE TABLE hotel_chain (
  hotel_chain_id INT NOT NULL,
  hotel_chain_name VARCHAR(45) NULL,
  hotel_chain_contact_number VARCHAR(12) NULL,
  hotel_chain_email_address VARCHAR(45) NULL,
  PRIMARY KEY (hotel_chain_id)
  );
    
  ------------------------------------------------
-- Table `hotel`
-- -----------------------------------------------------


CREATE TABLE hotel (
  hotel_id INT NOT NULL,
  hotel_name VARCHAR(45) NULL,
  hotel_contact_number VARCHAR(12) NULL,
  hotel_email_address VARCHAR(45) NULL,
  hotel_website VARCHAR(45) NULL,
  hotel_description VARCHAR(100) NULL,
  hotel_floor_count INT NULL,
  hotel_room_capacity INT NULL,
  hotel_chain_id INT NULL,
  addresses_address_id INT NOT NULL,
  check_in_time TIME NULL,
  check_out_time TIME NULL,
  PRIMARY KEY (hotel_id, addresses_address_id),
  CONSTRAINT fk_hotel_addresses
    FOREIGN KEY (addresses_address_id)
    REFERENCES addresses (address_id)
    );
 

-- -----------------------------------------------------
-- Table room_type
-- -----------------------------------------------------

CREATE TABLE room_type (
  room_type_id INT NOT NULL,
  room_type_name VARCHAR(45) NULL,
  room_cost DECIMAL(10,2) NULL,
  room_type_description VARCHAR(100) NULL,
  PRIMARY KEY (room_type_id)
  );


-- -----------------------------------------------------
-- Table rooms
-- -----------------------------------------------------

CREATE TABLE rooms (
  room_id INT NOT NULL,
  room_number INT NULL,
  rooms_type_rooms_type_id INT NOT NULL,
  hotel_hotel_id INT NOT NULL,
  PRIMARY KEY (room_id, rooms_type_rooms_type_id, hotel_hotel_id),
  CONSTRAINT fk_rooms_hotel
    FOREIGN KEY (hotel_hotel_id)
    REFERENCES hotel (hotel_id),
  CONSTRAINT fk_rooms_rooms_type1
    FOREIGN KEY (rooms_type_rooms_type_id)
    REFERENCES  room_type (room_type_id)
    );

The message is explanatory. In the CREATE TABLE 'rooms' DDL where you've assigned the fk_rooms_hotel foreign key the table 'hotel' does not contain the column hotel_id. Perhaps it's named something different? — SteveC

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2020-08-12T14:59:23

The primary key of hotel is currently (hotel_id, addresses_address_id), so you can't reference it in a foreign key on just (hotel_id). You might want to change the PK of hotel to just (hotel_id).

there are not Primary or candidate keys in the referenced table 'hotel' that match the referencing column list in the foreign key 'fk_rooms_hotel'

1 Answers