Description:
Adding a generated column with the INDEX keyword tries always to add a primary key instead. Creating the index later works OK and is valid workaround.
In documentation, the INDEX attributes are not really addressed except here:
https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
"Other attributes may be given to indicate whether the column is indexed or can be NULL, or provide a comment. (Note that the order of these attributes differs from their order in nongenerated column definitions.)"
Same in 5.7 documentation.
How to repeat:
CREATE TABLE t1 (id INT NOT NULL,
c1 VARCHAR(20) AS ('foo') VIRTUAL KEY NULL, PRIMARY KEY (id));
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.
CREATE TABLE t1 (id INT NOT NULL,
c1 VARCHAR(20) AS ('foo') STORED KEY NULL, PRIMARY KEY (id));
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
CREATE TABLE t1 (id INT NOT NULL,
c1 VARCHAR(20) AS ('foo') STORED KEY NOT NULL, PRIMARY KEY (id));
ERROR 1068 (42000): Multiple primary key defined
CREATE TABLE t1 (id INT NOT NULL,
c1 VARCHAR(20) AS ('foo') VIRTUAL KEY NOT NULL, PRIMARY KEY (id));
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.
col_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[[NOT] NULL] [[PRIMARY] KEY]
Confused!
Suggested fix:
Workaround:
CREATE TABLE t1 (id INT NOT NULL,
c1 VARCHAR(20) AS ('foo') VIRTUAL, INDEX (c1), PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)
[[UNIQUE] KEY] = for indexes or candidate primary key
[[PRIMARY] KEY] = for creating primary key
Quite confusing if out of order.
