ERROR 1826: Duplicate foreign key constraint name

Question

thank you for your time. I am trying to build a database with with the following instructions

T1-Hotel (hotelNo, hotelName, city) T2-Room (roomNo, hotelNo, type, price) T3-Guest (guestNo, guestName, guestAddress) T4-Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

I get a Constraint duplication error but I cant tell there is a duplication in the schema.

CREATE TABLE `hotel_booking`.`Booking` (
    `hotelNo` VARCHAR(5) NOT NULL,
    `guestNo` VARCHAR(5) NOT NULL,
    `datefFrom` DATE NOT NULL,
    `dateTo` DATE NULL,
    `roomNo` VARCHAR(5) NULL,
    PRIMARY KEY (`hotelNo`, `guestNo`, `datefFrom`),
    INDEX `guestNo_idx` (`guestNo` ASC) VISIBLE,
    INDEX `roomNo_idx` (`roomNo` ASC) VISIBLE,
    CONSTRAINT `hotelNo`
        FOREIGN KEY (`hotelNo`)
        REFERENCES `hotel_booking`.`Hotel` (`hotelNo`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `guestNo`
        FOREIGN KEY (`guestNo`)
        REFERENCES `hotel_booking`.`Guest` (`guestNo`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `roomNo`
        FOREIGN KEY (`roomNo`)
        REFERENCES `hotel_booking`.`Room` (`roonNo`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);

I get the following error ERROR 1826: Duplicate foreign key constraint name 'hotelNo' What is wrong and how it can be corrected?

Answer 1

when you create a Constraint, its name is database wide. you can list all constraints of your database with:

select *
from information_schema.table_constraints
where constraint_schema = 'YOUR_DB'

you have this error because you choose to give the contraint name on this table the same name as another constraint on another table of the SAME database. You can for example prefix your constraint name with the name of the table so there is no more interferences between constraints of different table targeting the same TargetTable.TargetColumn.

Regards. Thierry Brémard