[MDEV-36199] create table with serial primary key - differing result if pk declared separately - Jira

XML

Word

Printable

create table products_spk (

  id serial primary key);

Quite conveniently and as a major enhancement over MySQL produces no secondary index.

| Table        | Create Table                                                        |

| products_spk | CREATE TABLE `products_spk` (                                       |

|              |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,                 |

|              |   PRIMARY KEY (`id`)                                                |

|              | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci  |

However if the PRIMARY KEY is declared separately we get a secondary duplicate index:

create table products (

  id serial,

  primary key(id));

| Table    | Create Table                                                        |

| products | CREATE TABLE `products` (                                           |

|          |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,                 |

|          |   PRIMARY KEY (`id`),                                               |

|          |   UNIQUE KEY `id` (`id`)                                            |

|          | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci  |

As a desired result the second form will not create the secondary unique key index.

(arose after boasting MariaDB advantage over MySQL and discovering this edgecase).

relates to

MDEV-18927 Failing ALTER TABLE ... ADD IF NOT EXISTS ( `col1` SERIAL ) modifies table definition

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