Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.10, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
Our documentation says:
"For UNIQUE keys, PRIMARY KEYs and FOREIGN KEYs, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages."
<https://mariadb.com/kb/en/library/create-table/>
But for primary keys this is not true, the constraint name is just silently ignored:
This CREATE succeeds without warning:
CREATE TABLE t1 (
|
id1 INT,
|
id2 INT,
|
CONSTRAINT `foo` PRIMARY KEY (id1),
|
CONSTRAINT `bar` UNIQUE KEY(id2)
|
);
|
When verifying with SHOW CREATE TABLE the constraint name
for the UNIQUE key is shown, but not that of the PRIMARY key:
MariaDB [test]> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id1` int(11) NOT NULL,
|
`id2` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id1`),
|
UNIQUE KEY `bar` (`id2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
INFORMATION_SCHEMA.TABLE_CONSTRAINTS also does not show the
chosen name `foo` for the primary key:
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
|
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
|
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
|
| def | test | PRIMARY | test | t1 | PRIMARY KEY |
|
| def | test | bar | test | t1 | UNIQUE |
|
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
|
And the name is not shown in primary key related error messages either, contrary to what the knowledge base claims:
MariaDB [test]> insert into t1 values(1,1);
|
Query OK, 1 row affected (0.004 sec)
|
|
|
MariaDB [test]> insert into t1 values(2,1);
|
ERROR 1062 (23000): Duplicate entry '1' for key 'bar'
|
|
|
MariaDB [test]> insert into t1 values(1,2);
|
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
|
My main concern though is that there is no warning on CREATE about an unsupported feature being used and the chosen name being discarded in favour of the hardcoded "PRIMARY" name string.