Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17544

No warning when trying to name a primary key constraint

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            holyfoot Alexey Botchkov
            hholzgra Hartmut Holzgraefe
            Votes:
            1 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.