1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
DROP TABLE IF EXISTS t1,t2,t3;
set sql_warnings = 0;
SET @@session.storage_engine = 'InnoDB';
# - UNIQUE KEY
# - INDEX
# - FOREIGN INDEX (partially supported)
# - CHECK (allowed but not used)
# UNIQUE
create table t1 (a int, b virtual int as (a*2) unique);
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
create table t1 (a int, b virtual int as (a*2) stored unique);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` VIRTUAL int AS (a*2) STORED,
UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB
describe t1;
Field Type Null Key Default Extra
a int YES NULL
b int YES UNI NULL VIRTUAL
drop table t1;
create table t1 (a int, b virtual int as (a*2), unique key (b));
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
create table t1 (a int, b virtual int as (a*2) stored, unique (b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` VIRTUAL int AS (a*2) STORED,
UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB
describe t1;
Field Type Null Key Default Extra
a int YES NULL
b int YES UNI NULL VIRTUAL
drop table t1;
create table t1 (a int, b virtual int as (a*2));
alter table t1 add unique key (b);
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
drop table t1;
create table t1 (a int, b virtual int as (a*2) stored);
alter table t1 add unique key (b);
drop table t1;
# Testing data manipulation operations involving UNIQUE keys
# on virtual columns can be found in:
# - vcol_ins_upd.inc
# - vcol_select.inc
#
# INDEX
create table t1 (a int, b virtual int as (a*2), index (b));
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
create table t1 (a int, b virtual int as (a*2), index (a,b));
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
create table t1 (a int, b virtual int as (a*2) stored, index (b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` VIRTUAL int AS (a*2) STORED,
KEY `b` (`b`)
) ENGINE=InnoDB
describe t1;
Field Type Null Key Default Extra
a int YES NULL
b int YES MUL NULL VIRTUAL
drop table t1;
create table t1 (a int, b virtual int as (a*2) stored, index (a,b));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` VIRTUAL int AS (a*2) STORED,
KEY `a` (`a`,`b`)
) ENGINE=InnoDB
describe t1;
Field Type Null Key Default Extra
a int YES MUL NULL
b int YES NULL VIRTUAL
drop table t1;
create table t1 (a int, b virtual int as (a*2));
alter table t1 add index (b);
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
alter table t1 add index (a,b);
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
drop table t1;
create table t1 (a int, b virtual int as (a*2) stored);
alter table t1 add index (b);
drop table t1;
create table t1 (a int, b virtual int as (a*2) stored);
alter table t1 add index (a,b);
create table t2 like t1;
drop table t2;
drop table t1;
# Testing data manipulation operations involving INDEX
# on virtual columns can be found in:
# - vcol_select.inc
# FOREIGN KEY
# Rejected FK options.
create table t1 (a int, b virtual int as (a+1) stored,
foreign key (b) references t2(a) on update set null);
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a virtual column.
create table t1 (a int, b virtual int as (a+1) stored,
foreign key (b) references t2(a) on update cascade);
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a virtual column.
create table t1 (a int, b virtual int as (a+1) stored,
foreign key (b) references t2(a) on delete set null);
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a virtual column.
create table t1 (a int, b virtual int as (a+1) stored);
alter table t1 add foreign key (b) references t2(a) on update set null;
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a virtual column.
alter table t1 add foreign key (b) references t2(a) on update cascade;
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a virtual column.
alter table t1 add foreign key (b) references t2(a) on delete set null;
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a virtual column.
drop table t1;
create table t1 (a int, b virtual int as (a+1),
foreign key (b) references t2(a));
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
create table t1 (a int, b virtual int as (a+1));
alter table t1 add foreign key (b) references t2(a);
ERROR HY000: Key/Index cannot be defined on a non-stored virtual column.
drop table t1;
# Allowed FK options.
create table t2 (a int primary key, b char(5));
create table t1 (a int, b virtual int as (a % 10) stored,
foreign key (b) references t2(a) on update restrict);
drop table t1;
create table t1 (a int, b virtual int as (a % 10) stored,
foreign key (b) references t2(a) on update no action);
drop table t1;
create table t1 (a int, b virtual int as (a % 10) stored,
foreign key (b) references t2(a) on delete restrict);
drop table t1;
create table t1 (a int, b virtual int as (a % 10) stored,
foreign key (b) references t2(a) on delete cascade);
drop table t1;
create table t1 (a int, b virtual int as (a % 10) stored,
foreign key (b) references t2(a) on delete no action);
drop table t1;
# Testing data manipulation operations involving FOREIGN KEY
# on virtual columns can be found in:
# - vcol_ins_upd.inc
# - vcol_select.inc
#
# TODO: CHECK
DROP TABLE IF EXISTS t1,t2,t3;
set sql_warnings = 0;
|