7

Since SERIAL is shorthand for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, it looks like SERIAL PRIMARY KEY doubles up on the UNIQUE and PRIMARY KEY:

> CREATE TEMPORARY TABLE foo (id SERIAL PRIMARY KEY);

> SHOW CREATE TABLE foo \G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

There's definitely the wtf factor of seeing both PRIMARY KEY and UNIQUE KEY id in SHOW CREATE TABLE, but is that really two physical indexes I'm seeing?

Should one avoid using PRIMARY KEY with SERIAL and instead opt for writing bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, or using SERIAL with no primary keys on the table?

  • 2
    Ugh! File a bug at bugs.mysql.com . – Rick James Aug 21 '17 at 3:06
  • Yeah, this looks like a mysql bug... – Amalgovinus Mar 29 '18 at 0:53
  • I'm also seeing this behavior in 5.7.18 MySQL Community Server – Kevin May 22 '18 at 20:18
  • 1
    The phenomena does not occur in 10.2.14-MariaDB. – Lennart May 22 '18 at 20:49
  • This can be regarded as a bug. Its reason is obvious - auto_increment must be a key (server error 1075). But the source is somebody's lasiness - there are aliases serial and serial default value, and there is no alias serial primary key... – Akina May 23 '18 at 5:43
1

I'm also seeing this behavior in 5.7.18 MySQL Community Server.

Based on the output from SHOW INDEX FROM foo there is a duplicate index being created and maintained:

mysql> CREATE TEMPORARY TABLE foo (id SERIAL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> show index from foo;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| foo   |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| foo   |          0 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

Edit: This also happens for normal tables:

mysql> CREATE TABLE foo (id SERIAL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> show index from foo;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| foo   |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| foo   |          0 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'foo';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | my_db             | PRIMARY         | my_db        | foo        | PRIMARY KEY     |
| def                | my_db             | id              | my_db        | foo        | UNIQUE          |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)
  • 1
    Does this happen for an ordinary table as well, i.e. CREATE TABLE foo ...? If so, what is the content of INFORMATION_SCHEMA.TABLE_CONSTRAINTS for the table? – Lennart May 22 '18 at 20:52
  • @Lennart Yes, and I added the contents of INFORMATION_SCHEMA.TABLE_CONSTRAINTS that you requested. – Kevin May 22 '18 at 23:28
  • Ok, so then it's not a bug in SHOW ..., it does create two constraints. – Lennart May 23 '18 at 3:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.