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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
|
create table t1 (
a int not null references t2,
b int not null references t2 (c),
primary key (a,b),
foreign key (a) references t3 match full,
foreign key (a) references t3 match partial,
foreign key (a,b) references t3 (c,d) on delete no action
on update no action,
foreign key (a,b) references t3 (c,d) on update cascade,
foreign key (a,b) references t3 (c,d) on delete set default,
foreign key (a,b) references t3 (c,d) on update set null) engine=myisam;
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
create table t1 (
a int not null,
b int not null references t2 (c),
primary key (a,b),
foreign key (a,b) references t3 (c,d) on delete no action
on update no action,
foreign key (a,b) references t3 (c,d) on update cascade,
foreign key (a,b) references t3 (c,d) on delete set default,
foreign key (a,b) references t3 (c,d) on update set null) engine=myisam;
create index a on t1 (a);
create unique index b on t1 (a,b);
drop table t1;
#
# 5) Check how parent table in different storage engine is handled.
#
# We handle it in the same way as if table was missing to be compatible
# with pre-8.0 versions.
#
# 5.a) Attempt to create table with a foreign key or to add foreign key
# to already xisting table which reference parent in different SE
# should fail in FOREIGN_KEY_CHECKS=1 mode.
CREATE TABLE bad_parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES bad_parent(pk)) ENGINE=InnoDB;
ERROR HY000: Failed to open the referenced table 'bad_parent'
CREATE TABLE child (fk INT) ENGINE=InnoDB;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES bad_parent(pk);
ERROR HY000: Failed to open the referenced table 'bad_parent'
#
# 5.b) However, in FOREIGN_KEY_CHECKS=0 mode this is allowed.
# Referenced table is considered missing and existing table
# in a wrong SE is not considered as real parent.
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES bad_parent(pk);
DROP TABLE child;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES bad_parent(pk));
SET FOREIGN_KEY_CHECKS = 1;
# Bad parent can be dropped just fine even in FOREIGN_KEY_CHECKS=1 mode.
DROP TABLE bad_parent;
DROP TABLE child;
#
# 5.c) Attempt to change SE for table participating in foreign key
# should lead to error.
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk)) ENGINE=InnoDB;
ALTER TABLE child ENGINE=MyISAM;
ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
ALTER TABLE parent ENGINE=MyISAM;
ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
DROP TABLES child, parent;
#
# 5.d) It is possible to add "parent" in wrong SE to orphan foreign key.
# However, it won't be considered as real parent.
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES bad_parent(pk));
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE bad_parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
# It is possible to break bad parent even in FOREIGN_KEY_CHECKS=1 mode.
ALTER TABLE bad_parent DROP COLUMN pk, ADD COLUMN i INT;
DROP TABLE bad_parent;
DROP TABLE child;
#
# 7) Test that we check that referencing and referenced column types are
# compatible. Such check should be performed for newly created foreign
# keys and when we change types of columns in existing foreign keys.
#
#
# 7.d) It should be impossible to create foreign keys with incompatible
# column types by adding parent to previously orphan foreign key.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk)) ENGINE=InnoDB;
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=InnoDB;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
CREATE TABLE parent0 (pk INT PRIMARY KEY) ENGINE=InnoDB;
ALTER TABLE parent0 RENAME TO parent;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
ALTER TABLE parent0 RENAME TO parent, ADD COLUMN j INT;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
RENAME TABLE parent0 TO parent;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLES child, parent0;
SET FOREIGN_KEY_CHECKS=1;
#
# Tests for bug#28608460 "POSSIBLE TO ADD INCONSISTENT PARENT TO ORPHAN
# FOREIGN KEY IF SE CHANGED".
#
#
# Check that consistency checks are performed when we add parent
# table to previously orphan foreign key by changing table storage
# engine.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE child (fk INT, FOREIGN KEY(fk) REFERENCES parent (pk)) ENGINE=InnoDB;
CREATE TABLE parent (pk INT) ENGINE=MyISAM;
ALTER TABLE parent ENGINE=InnoDB;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE parent;
CREATE TABLE parent (a INT) ENGINE=MyISAM;
ALTER TABLE parent ENGINE=InnoDB;
ERROR HY000: Failed to add the foreign key constraint. Missing column 'pk' for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE parent;
CREATE TABLE parent (pk BIGINT PRIMARY KEY) ENGINE=MyISAM;
ALTER TABLE parent ENGINE=InnoDB;
ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_ibfk_1' are incompatible.
DROP TABLE parent;
#
# This should work for ALTER TABLE that combines change of SE
# with renaming of table.
CREATE TABLE parent0 (pk INT) ENGINE=MyISAM;
ALTER TABLE parent0 ENGINE=InnoDB, RENAME TO parent;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE parent0;
#
# ALTER TABLE which combines change of SE and renaming of the
# table is executed as if we separately did change of SE and
# renamed table after that. I.e. consistency checks are also
# performed for orphan foreign keys which are associated with
# old table name.
CREATE TABLE parent (pk INT) ENGINE=MyISAM;
ALTER TABLE parent ENGINE=InnoDB, RENAME TO parent0;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'child_ibfk_1' in the referenced table 'parent'
DROP TABLE parent;
#
# Check that unique constraint name is correctly updated when parent
# table is added to orphan foreign key by changing table SE.
CREATE TABLE parent (pk INT, UNIQUE u(pk)) ENGINE=MyISAM;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME UNIQUE_CONSTRAINT_NAME
parent NULL
ALTER TABLE parent ENGINE=InnoDB;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME UNIQUE_CONSTRAINT_NAME
parent u
DROP TABLE parent;
#
# Since ALTER TABLE which combines change of SE and renaming of the
# table is executed as if we separately did change of SE and
# renamed table after that, the referenced table name should
# be updated too.
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME UNIQUE_CONSTRAINT_NAME
parent NULL
ALTER TABLE parent ENGINE=InnoDB, RENAME TO parent0;
SELECT referenced_table_name, unique_constraint_name FROM
information_schema.referential_constraints WHERE table_name = 'child';
REFERENCED_TABLE_NAME UNIQUE_CONSTRAINT_NAME
parent0 PRIMARY
RENAME TABLE parent0 TO parent;
DROP TABLES parent;
#
# Check that under LOCK TABLES, when adding parent for previously orphan
# foreign key by SE change, we check locks on child tables.
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=MyISAM;
LOCK TABLES parent WRITE;
ALTER TABLE parent ENGINE=InnoDB;
ERROR HY000: Table 'child' was not locked with LOCK TABLES
UNLOCK TABLES;
LOCK TABLES child READ, parent WRITE;
ALTER TABLE parent ENGINE=InnoDB;
ERROR HY000: Table 'child' was locked with a READ lock and can't be updated
UNLOCK TABLES;
LOCK TABLES child WRITE, parent WRITE;
ALTER TABLE parent ENGINE=InnoDB;
UNLOCK TABLES;
DROP TABLES child, parent;
SET FOREIGN_KEY_CHECKS=1;
#
# Additional coverage for bug#30267236 "REPLACE DICT_FOREIGN_PARSE...
# WITH SQL-LAYER CHECK."
#
# Special case. We silently ignore requests to drop foreign keys
# on tables in storage engines which don't support foreign keys.
# This is symmetrical with the fact that we silently ignore requests
# to create foreign keys on such tables.
CREATE TABLE parent (pk INT PRIMARY KEY);
CREATE TABLE child (fk1 INT, b INT, CONSTRAINT c FOREIGN KEY (fk1) REFERENCES parent (pk));
CREATE TABLE myisam_table (fk INT) ENGINE=MyISAM;
ALTER TABLE myisam_table DROP FOREIGN KEY no_such_fk;
ALTER TABLE myisam_table DROP FOREIGN KEY c;
DROP TABLES myisam_table, child, parent;
|
