Ошибка 1826 mysql workbench

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?

Normally I’d assume this is the fault of Microsoft Access, since Access frequently fails to conform to standard SQL. But this time it’s the fault of MySQL.

This is a bug in MySQL: https://bugs.mysql.com/bug.php?id=72751

Constraint names are supposed to be case-sensitive, per https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html:

Object names may be considered duplicates if their uppercase forms are equal according to a binary collation.

In other words, some object names are case-insensitive.

That is true for names of cursors, conditions, procedures, functions, savepoints, stored routine parameters, stored program local variables, and plugins. It is not true for names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables.

That says that constraint names are one of the object types whose names are supposed to case-sensitive.

But that documentation is incorrect. Currently MySQL treats constraint names as case-insensitive. So constraints named idMarca and IdMarca conflict.

We can demo this:

mysql> create table parent (id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> create table child1 (parent int, constraint con foreign key (parent) references parent(id));
Query OK, 0 rows affected (0.01 sec)

mysql> create table child2 (parent int, constraint Con foreign key (parent) references parent(id));
ERROR 1826 (HY000): Duplicate foreign key constraint name 'Con'

That’s the error message in MySQL 8.0. The names con and Con should be treated as distinct, but they aren’t.

In older versions of MySQL, the error message wasn’t clear, something like «1050: Table ‘./test/child2’ already exists».

How to fix this? You’ll have to create your foreign key constraints manually.

This bug was reported in 2014, and hasn’t been fixed, so I wouldn’t get your hopes up that it will be fixed soon. You may click the «Affects Me» button in the bug tracker to vote for it to get some attention, but don’t count on it. Just fix the constraint names in your project.

Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `xq`.`manageinfo` 
ADD CONSTRAINT `idmanagetype`
  FOREIGN KEY (`idmanagetype`)
  REFERENCES `xq`.`managetype` (`idmanagetype`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ERROR 1826: Duplicate foreign key constraint name 'idmanagetype'
SQL Statement:
ALTER TABLE `xq`.`manageinfo` 
ADD CONSTRAINT `idmanagetype`
  FOREIGN KEY (`idmanagetype`)
  REFERENCES `xq`.`managetype` (`idmanagetype`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION

1826 г. — Нить не отменялась. …Вот этот. .
Причина: я уже добавлял индекс внешнего ключа с именем idmanagetype раньше. Если он будет повторяться, будет сообщено об ошибке. Я добавил 1 после имени и успешно.

ALTER TABLE `xq`.`manageinfo` 
ADD INDEX `idmanagetype1_idx` (`idmanagetype` ASC) VISIBLE;
;
ALTER TABLE `xq`.`manageinfo` 
ADD CONSTRAINT `idmanagetype1`
  FOREIGN KEY (`idmanagetype`)
  REFERENCES `xq`.`managetype` (`idmanagetype`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Итак, я пытаюсь создать базу данных. У меня есть эта ошибка для дублированного внешнего ключа, и я ничего не могу сказать. Вот моя база данных
СОЗДАТЬ СХЕМУ sjdupl01CECS535Project;

CREATE TABLE `sjdupl01CECS535Project`.`HOTEL` (
  `hotelid` INT NOT NULL,
  `number` VARCHAR(45) NULL,
  `street` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `zip` VARCHAR(45) NULL,
  `manager-name` VARCHAR(45) NULL,
  `number-rooms` INT UNSIGNED NULL,
  `has-pool` TINYINT NULL,
  `has-bar` TINYINT NULL,
  `has-restaurant` TINYINT NULL,
  PRIMARY KEY (`hotelid`));

CREATE TABLE `sjdupl01CECS535Project`.`ROOM` (
  `type` ENUM('regular', 'extra', 'suite', 'business', 'luxury', 'family') NOT NULL,
  `occupancy` INT NULL,
  `number-beds` INT UNSIGNED NULL,
  `type-beds` VARCHAR(45) NULL,
  `price` INT NULL,
  PRIMARY KEY (`type`));

CREATE TABLE `sjdupl01CECS535Project`.`ROOMHOTEL` (
  `hotelid` INT NOT NULL,
  `room-type` ENUM('regular', 'extra', 'suite', 'business', 'luxury', 'family') NOT NULL,
  `number` INT UNSIGNED NULL,
  PRIMARY KEY (`hotelid`, `room-type`),
  INDEX `room-type_idx` (`room-type` ASC) VISIBLE,
  CONSTRAINT `hotelid`
    FOREIGN KEY (`hotelid`)
    REFERENCES `sjdupl01CECS535Project`.`HOTEL` (`hotelid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `room-type`
    FOREIGN KEY (`room-type`)
    REFERENCES `sjdupl01CECS535Project`.`ROOM` (`type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `sjdupl01CECS535Project`.`CUSTOMER` (
  `cust-id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `number` VARCHAR(45) NULL,
  `street` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `zip` VARCHAR(45) NULL,
  `status` ENUM('gold', 'silver', 'business') NULL,
  PRIMARY KEY (`cust-id`));

CREATE TABLE `sjdupl01CECS535Project`.`RESERVATION` (
  `hotel-id` INT NOT NULL,
  `cust-id` INT NOT NULL,
  `room-type` ENUM('regular', 'extra', 'suite', 'business', 'luxury', 'family') NOT NULL,
  `begin-date` DATE NULL,
  `end-date` DATE NULL,
  `credit-card-number` VARCHAR(45) NULL,
  `exp-date` VARCHAR(45) NULL,
  PRIMARY KEY (`hotel-id`, `cust-id`, `room-type`),
  INDEX `cust-id_idx` (`cust-id` ASC) VISIBLE,
  INDEX `room-type_idx` (`room-type` ASC) VISIBLE,
  CONSTRAINT `hotel-id`
    FOREIGN KEY (`hotel-id`)
    REFERENCES `sjdupl01CECS535Project`.`HOTEL` (`hotelid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cust-id`
    FOREIGN KEY (`cust-id`)
    REFERENCES `sjdupl01CECS535Project`.`CUSTOMER` (`cust-id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `room-type`
    FOREIGN KEY (`room-type`)
    REFERENCES `sjdupl01CECS535Project`.`ROOM` (`type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ОШИБКА 1826: повторяющееся имя ограничения внешнего ключа ‘room-type’
Таким образом, ошибка возникает на последней таблице (БРОНИРОВАНИЕ).
Я не вижу дублирования типа «номер».

В чем проблема?

I also struggled with this problem for quite a while.

I came accross this interesting thread from MySQL forum: http://forums.mysql.com/read.php?11,11388,11388#msg-11388

I also came accross (obviously) some good SO Q/A.

It seems that the message mentioned in «user948950» ‘s question can be coming from a wide range of reasons: log file too big, incorrect mysql.ini file values, spaces in the file path, security/acl issue, old entries in the registry, and so on.

So, after trying for 3h to fix this… I abandonned and decided to do a good old re-install.

This is where this post from (again) this MySQL thread came in useful, I quote:

Gary Williams wrote: Hi Guys,

I’ve had exactly the same problem and this is how I got it working
for me, starting with a non working installation.

1. Stop the windows service for any existing mysql installation.

2. Uninstall Mysql.

As with most uninstalls, old files are left behind. If your directory
is C:mysql etc then delete the innob, etc, files but leave the
directories themselves as well as any existing databases in ‘data’.
If your directory is C:Program Files etc, delete all the mysql
directories.

1. It’s now worth running regedit to make sure the old registry entries are deleted as well by the uninstall. If not, delete them.

2. It’s ok to use the new .msi installer (essential files only), however ….

3. Do not use their default install path! Some genius set a path with spaces in it! Choose the custom install and select a sensible path,
   ie, C:mysql (note from Adrien: C:mysqldata for … the data)

4. Do not choose to alter the security settings. Uncheck the relevant box and the install will complete without having to set a root
   password.

I think I have remembered everything.

Good luck

Gary

I did get into troubles when simply copy/pasting the databases I had in my previous «data» directory to the new one. So the work around I found was to export each database (I know… a lot of fun) and then re-import them one by one.

FYI: I used the following command to import C:/<MySQLInstallDir>/My SQL Server x.x/bin/mysql -u root -p <dbName> < "<dirPathOfDump><dumpName>.sql", that is for instance C:/mysql/MySQL Server 5.6/bin/mysql -u root -p mySupaCoolDb < "C:mySupaCoolDbDump20130901.sql"

I also struggled with this problem for quite a while.

I came accross this interesting thread from MySQL forum: http://forums.mysql.com/read.php?11,11388,11388#msg-11388

I also came accross (obviously) some good SO Q/A.

It seems that the message mentioned in «user948950» ‘s question can be coming from a wide range of reasons: log file too big, incorrect mysql.ini file values, spaces in the file path, security/acl issue, old entries in the registry, and so on.

So, after trying for 3h to fix this… I abandonned and decided to do a good old re-install.

This is where this post from (again) this MySQL thread came in useful, I quote:

Gary Williams wrote: Hi Guys,

I’ve had exactly the same problem and this is how I got it working
for me, starting with a non working installation.

1. Stop the windows service for any existing mysql installation.

2. Uninstall Mysql.

As with most uninstalls, old files are left behind. If your directory
is C:mysql etc then delete the innob, etc, files but leave the
directories themselves as well as any existing databases in ‘data’.
If your directory is C:Program Files etc, delete all the mysql
directories.

1. It’s now worth running regedit to make sure the old registry entries are deleted as well by the uninstall. If not, delete them.

2. It’s ok to use the new .msi installer (essential files only), however ….

3. Do not use their default install path! Some genius set a path with spaces in it! Choose the custom install and select a sensible path,
   ie, C:mysql (note from Adrien: C:mysqldata for … the data)

4. Do not choose to alter the security settings. Uncheck the relevant box and the install will complete without having to set a root
   password.

I think I have remembered everything.

Good luck

Gary

I did get into troubles when simply copy/pasting the databases I had in my previous «data» directory to the new one. So the work around I found was to export each database (I know… a lot of fun) and then re-import them one by one.

FYI: I used the following command to import C:/<MySQLInstallDir>/My SQL Server x.x/bin/mysql -u root -p <dbName> < "<dirPathOfDump><dumpName>.sql", that is for instance C:/mysql/MySQL Server 5.6/bin/mysql -u root -p mySupaCoolDb < "C:mySupaCoolDbDump20130901.sql"