ERROR 1826: Duplicate foreign key constraint name

user_12096025 0
Viewed 16156

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?Columns and keys

foreign keys

mysql-workbench schema sql
2 Answers

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

user_146745630

Your constraint_name is same as your column name leading to duplicate/ambiguity change your foreignkey constraint name to some other name like constraint hello1... which is different from your column name

  CONSTRAINT `hotelNo1`
    FOREIGN KEY (`hotelNo`)

similarly for others

user_104974830
Related