I am trying to create three tables :

1. Hotel Table

    CREATE TABLE Hotels(
    HotelID VARCHAR(30) NOT NULL,
    Name VARCHAR(30) NOT NULL,
    City VARCHAR(30) NOT NULL,
    Rating VARCHAR(30) NOT NULL,
    Description VARCHAR(500) NOT NULL,
    PRIMARY KEY (HotelID)
    
    )

2. Rooms table

    CREATE TABLE Rooms (
    HotelID VARCHAR(30) NOT NULL,
    Room_Number INT NOT NULL,
    Price_Rate INT NOT NULL,
    Type VARCHAR(30) NOT NULL,
    PRIMARY KEY(HotelID, Room_Number),
    FOREIGN KEY(HotelID) REFERENCES hotels(HotelID)
    )

3. BookingInfo Table

    CREATE TABLE BookingInfo (
    HotelID VARCHAR(30) NOT NULL,
    UserID VARCHAR(30) NOT NULL,
    Room_Number INT NOT NULL,
    Arrival_Date DATE NOT NULL,
    Departure_Date DATE NOT NULL,
    PRIMARY KEY(UserID, HotelID, Room_Number, Arrival_Date),
    FOREIGN KEY(HotelID) REFERENCES hotels(HotelID),
    FOREIGN KEY(UserID) REFERENCES users(UserID),
    FOREIGN KEY (Room_Number) REFERENCES rooms(Room_Number)
    )

But when I try to create the third table I am getting errno: 150 "Foreign key constraint is incorrectly formed".

Moreover, when I delete FOREIGN KEY (Room_Number) REFERENCES rooms(Room_Number) the table gets created successfully. But I don't understand why it is not accepting FOREIGN KEY (Room_Number) REFERENCES rooms(Room_Number).

I have also tried to alter the table and change data type but still didn't get any success. Can anyone please point out my the mistake in this codes.

CodePudding user response：

The primary key of Rooms is two columns: (HotelID, Room_Number).

The foreign key in BookingInfo is one column: (Room_Number).

A foreign key should reference the whole primary key of the referenced table. If that primary key has more than one column, all of the columns must be referenced by the foreign key, and in the same order.

Caveat that is not standard SQL and specific to InnoDB: InnoDB allows a foreign key to reference part of a multi-column key in the referenced table, as long as the column is the leftmost column of that key. But in your case, you are referencing the second from the left column of that key, Room_Number. So it's not allowed. And in my opinion, it's a bad idea anyway to reference part of the key, because it leads to weird data anomalies.

CodePudding user response：

REFERENCES rooms(Room_Number) room_number has to be a key or the first node of a multi column key

'In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.' https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

CodePudding user response：

Thanks a lot for the guidance. This code has worked for me :

CREATE TABLE BookingInfo (
    HotelID VARCHAR(30) NOT NULL,
    UserID VARCHAR(30) NOT NULL,
    Room_Number INT NOT NULL,
    Arrival_Date DATE NOT NULL,
    Departure_Date DATE NOT NULL,
    PRIMARY KEY(UserID, HotelID, Room_Number, Arrival_Date),
    FOREIGN KEY(UserID) REFERENCES users(UserID),
    FOREIGN KEY(HotelID,Room_Number) REFERENCES rooms(HotelID,Room_Number)
    
    )