1
# This is the test for bug 51378. Unique index created
2
# through "create index" and "alter table add unique index"
3
# interfaces should not be treated as primary index if indexed
4
# columns contain one or more column prefix(es) (only prefix/part of
5
# the column is indexed)
6
# On the other hand, if there is a unique index covers all
7
# columns of a table, and they are non-null columns, and
8
# full length of the column are indexed, then this index
9
# will be created as primary index
10
# Following queries test various scenario, no mismatch
11
# error message should be printed.
12
-- source include/have_innodb_plugin.inc
14
# Create a table contains a BLOB column
15
create table bug51378 (
18
col3 time not null) engine = innodb;
20
# Create following unique indexes on 'col1' and 'col2(31)'
21
# of the table, the index should not be treated as primary
22
# key because it indexes only first 31 bytes of col2.
23
# Thus it contains "column prefix", and will not be
24
# upgraded to primary index.
25
# There should not be mismatch message printed in the
27
create unique index idx on bug51378(col1, col2(31));
29
alter table bug51378 add unique index idx2(col1, col2(31));
31
# Unique index on 'col1' and 'col3' will be created as primary index,
32
# since the index does not contain column prefix
33
create unique index idx3 on bug51378(col1, col3);
35
# Show create table would show idx3 created as unique index, internally,
36
# idx3 is treated as primary index both by MySQL and Innodb
37
SHOW CREATE TABLE bug51378;
39
# "GEN_CLUST_INDEX" will be re-created as default primary index
40
# after idx3 is dropped
41
drop index idx3 on bug51378;
43
SHOW CREATE TABLE bug51378;
45
# Or we can add the primary key through alter table interfaces
46
alter table bug51378 add primary key idx3(col1, col2(31));
48
SHOW CREATE TABLE bug51378;
52
# Or we can create such primary key through create table interfaces
53
create table bug51378 (
56
col3 time not null, primary key(col1, col2(31))) engine = innodb;
58
# Unique index on one or more column prefix(es) will be created
59
# as non-cluster index
60
create unique index idx on bug51378(col1, col2(31));
62
SHOW CREATE TABLE bug51378;
66
# If a table has a NULLABLE column, unique index on it will not
67
# be treated as primary index.
68
create table bug51378 (
70
col2 int ) engine = innodb;
72
# This will be created as non-cluster index since col2 is nullable
73
create unique index idx on bug51378(col1, col2);
75
SHOW CREATE TABLE bug51378;