Bug #35320 Wrong semantics of multi-component constraint name
Submitted: 16 Mar 2008 12:45 Modified: 17 Mar 2008 5:44
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1 OS:Any
Assigned to: CPU Architecture:Any

[16 Mar 2008 12:45] Dmitry Lenev
MySQL allows to specify multi-component constraint names (i.e. names consisting of several identifiers separated by '.'), for example, for foreign keys, but interprets such names in non-standard and impractical fashion.

SQL-standard says that multi-component constraint name should be either schema.constraint or catalog.schema.constraint, where schema and catalog
should be schema and catalog of the table on which constraint is created.

Whereas MySQL interprets such names as table.constraint or database.table.constraint correspondingly and requires that database and table part of the name are the same as for table for which constraint is created.

How to repeat:
use test;
create table t1 (i int primary key);
create table t2 (i int, constraint test.c foreign key (i) references t1 (i));
# Above statement emits "ERROR 42000: Incorrect table name 'test'"
create table t2 (i int, constraint std.test.c foreign key (i) references t1 (i));
# Above statement emits "ERROR 42000: Incorrect database name 'std'"

Suggested fix:
Fix server to follow standard semantics.
[17 Mar 2008 5:44] Valeriy Kravchuk
Thank you for a problem report.
[8 Oct 2019 1:28] Federico Razzoli
In 8.0 both those statements fail with a 1064 syntax error. If this behavior is correct, the bug seems to be fixed.