[MDEV-17544] No warning when trying to name a primary key constraint Created: 2018-10-25  Updated: 2020-08-25  Resolved: 2019-07-31

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, Documentation
Affects Version/s: 5.5, 10.0, 10.1, 10.3.10, 10.2, 10.3, 10.4
Fix Version/s: 10.3.18, 10.4.8

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Alexey Botchkov
Resolution: Fixed Votes: 1
Labels: upstream


 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.

 Comments   
Comment by Alexander Keremidarski [ 2018-10-25 ]

I edited the CREATE TABLE page in the documentation to clarify that for PRIMARY KEY the name is accepted but ignored. This should be also explained at CONSTRAINT page https://mariadb.com/kb/en/library/constraint/ where the syntax does not even mention index_name can be specified for PRIMARY KEY

The server definitely should return warning so it is not documentation only problem.

Comment by Elena Stepanova [ 2018-11-02 ]

Assigning to serg for re-distribution.

Comment by Alexey Botchkov [ 2019-07-04 ]

http://lists.askmonty.org/pipermail/commits/2019-July/013889.html

Comment by Sergei Golubchik [ 2019-07-15 ]

This is good, just one detail — you cannot add new errors or warnings to 10.3, because 10.4 is already GA, and adding new warnings to 10.3 would cause 10.4 error numbers to change.

So, just use, say,

      push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
 
                          ER_WRONG_NAME_FOR_INDEX,
 
                          "Name '%-.100s' ignored for PRIMARY key.",
 
                          key->name.str);

Comment by Alexey Botchkov [ 2019-07-31 ]

https://github.com/MariaDB/server/commit/c6efbc543d6fb3004f320ef9d02542a4692ca88a

Comment by Sergei Petrunia [ 2019-08-06 ]

You forgot to update .result files for MyRocks tests. Now fixed.

Generated at Thu Feb 08 08:37:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.