Why do MySQL give me a duplicate foreign key constraint name error (1826) if actually I haven't-CodePudding

I have imported in MySQL Workbench some tables as .csv files which were exported from Microsoft Access, and then i have set some relations. When I try to Synchronize Model I get this strange error: Error 1826: Duplicate foreign key constraint name 'idMarca'. I say strange because I don't have any kind of explanation to this, as there isn't any duplicated foreign key name in the diagram (actually idMarca in "Productos" is not duplicated because in table "Ingresos" its name starts with capital letter). enter image description here Thanks in advance if someone could help me.

I tried searching for the duplicate foreign key, but haven't found it.

CodePudding user response：

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.