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
151
152
153
154
155
156
157
158
159
160
161
162
163
|
################################################################################
# inc/vcol_keys.inc #
# #
# Purpose: #
# Testing keys, indexes defined upon virtual columns. #
# #
# #
# #
#------------------------------------------------------------------------------#
# Original Author: Andrey Zhakov #
# Original Date: 2008-09-02 #
# Change Author: Oleksandr Byelkin (Monty program Ab)
# Date: 2009-03-24
# Change: Syntax changed
################################################################################
--echo # - UNIQUE KEY
--echo # - INDEX
--echo # - FULLTEXT INDEX
--echo # - SPATIAL INDEX (not supported)
--echo # - FOREIGN INDEX (partially supported)
--echo # - CHECK (allowed but not used)
--echo # UNIQUE
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
create table t1 (a int, b int as (a*2) unique);
create table t1 (a int, b int as (a*2) persistent unique);
show create table t1;
describe t1;
drop table t1;
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
create table t1 (a int, b int as (a*2), unique key (b));
create table t1 (a int, b int as (a*2) persistent, unique (b));
show create table t1;
describe t1;
drop table t1;
create table t1 (a int, b int as (a*2));
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
alter table t1 add unique key (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add unique key (b);
drop table t1;
--echo # Testing data manipulation operations involving UNIQUE keys
--echo # on virtual columns can be found in:
--echo # - vcol_ins_upd.inc
--echo # - vcol_select.inc
--echo #
--echo # INDEX
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
create table t1 (a int, b int as (a*2), index (b));
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
create table t1 (a int, b int as (a*2), index (a,b));
create table t1 (a int, b int as (a*2) persistent, index (b));
show create table t1;
describe t1;
drop table t1;
create table t1 (a int, b int as (a*2) persistent, index (a,b));
show create table t1;
describe t1;
drop table t1;
create table t1 (a int, b int as (a*2));
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
alter table t1 add index (b);
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
alter table t1 add index (a,b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (a,b);
create table t2 like t1;
drop table t2;
drop table t1;
--echo # Testing data manipulation operations involving INDEX
--echo # on virtual columns can be found in:
--echo # - vcol_select.inc
--echo #
--echo # TODO: FULLTEXT INDEX
--echo # SPATIAL INDEX
if (!$skip_spatial_index_check)
{
--echo # Error "All parts of a SPATIAL index must be NOT NULL"
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
create table t1 (a int, b int as (a+1) persistent, spatial index (b));
create table t1 (a int, b int as (a+1) persistent);
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
alter table t1 add spatial index (b);
drop table t1;
}
--echo # FOREIGN KEY
--echo # Rejected FK options.
--error ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update set null);
--error ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update cascade);
--error ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on delete set null);
create table t1 (a int, b int as (a+1) persistent);
--error ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN
alter table t1 add foreign key (b) references t2(a) on update set null;
--error ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN
alter table t1 add foreign key (b) references t2(a) on update cascade;
--error ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN
alter table t1 add foreign key (b) references t2(a) on delete set null;
drop table t1;
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
create table t1 (a int, b int as (a+1),
foreign key (b) references t2(a));
create table t1 (a int, b int as (a+1));
--error ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
alter table t1 add foreign key (b) references t2(a);
drop table t1;
--echo # Allowed FK options.
create table t2 (a int primary key, b char(5));
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update no action);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete cascade);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete no action);
drop table t1;
--echo
--echo # Testing data manipulation operations involving FOREIGN KEY
--echo # on virtual columns can be found in:
--echo # - vcol_ins_upd.inc
--echo # - vcol_select.inc
--echo #
--echo # TODO: CHECK
|
