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
|
--source include/have_innodb.inc
#
# MDEV-8524: Improve error messaging when there is duplicate key or foreign key names
#
CREATE TABLE t1 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Below create table fails because constraint name test
# is reserved for above table.
#
--error ER_CANT_CREATE_TABLE
CREATE TABLE t2 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id),
CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
show warnings;
drop table t1;
#
# MDEV-6697: Improve foreign keys warnings/errors
#
#
# No index for referenced columns
#
create table t1(a int) engine=innodb;
--error ER_CANT_CREATE_TABLE
create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=innodb;
show warnings;
drop table t1;
create table t1(a int unique, b int) engine=innodb;
--error ER_CANT_CREATE_TABLE
create table t2(a int, b int, foreign key (a) references t1(a), foreign key (b) references t1(b)) engine=innodb;
show warnings;
drop table t1;
create table t1(a int not null primary key, b int) engine=innodb;
--error ER_CANT_CREATE_TABLE
create table t2(a int, b int, constraint a foreign key a (a) references t1(a),
constraint a foreign key a (a) references t1(b)) engine=innodb;
show warnings;
create table t2(a int, b int, constraint a foreign key a (a) references t1(a)) engine=innodb;
--error ER_CANT_CREATE_TABLE
alter table t2 add constraint b foreign key (b) references t2(b);
show warnings;
drop table t2, t1;
#
# Referenced table does not exists
#
create table t1 (f1 integer primary key) engine=innodb;
--error ER_CANT_CREATE_TABLE
alter table t1 add constraint c1 foreign key (f1) references t11(f1);
show warnings;
drop table t1;
#
# Foreign key on temporal tables
#
create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb;
# remove echos and uncomment the commented when MDEV-8569 is fixed
--echo create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
--echo ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
--echo show warnings;
--echo Level Code Message
--echo Warning 150 Create table `mysqld.1`.`t2` with foreign key constraint failed. Referenced table `mysqld.1`.`t1` not found in the data dictionary close to foreign key(a) references t1(a)) engine=innodb.
--echo Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
--echo Warning 1215 Cannot add foreign key constraint
--echo alter table t1 add foreign key(b) references t1(a);
--echo ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
--echo show warnings;
--echo Level Code Message
--echo Warning 150 Alter table `mysqld.1`.`t1` with foreign key constraint failed. Referenced table `mysqld.1`.`t1` not found in the data dictionary close to foreign key(b) references t1(a).
--echo Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
--echo Warning 1215 Cannot add foreign key constraint
--error ER_CANT_CREATE_TABLE
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
show warnings;
--error ER_CANT_CREATE_TABLE
alter table t1 add foreign key(b) references t1(a);
show warnings;
drop table t1;
#
# Column numbers do not match
#
create table t1(a int not null primary key, b int, key(b)) engine=innodb;
--error 1239
alter table t1 add foreign key(a,b) references t1(a);
show warnings;
drop table t1;
create table t1(a int not null primary key, b int, key(b)) engine=innodb;
--error 1239
alter table t1 add foreign key(a) references t1(a,b);
show warnings;
drop table t1;
#
# ON UPDATE/DELETE SET NULL on NOT NULL column
#
create table t1 (f1 integer not null primary key) engine=innodb;
--error ER_CANT_CREATE_TABLE
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null;
show warnings;
--error ER_CANT_CREATE_TABLE
create table t2(a int not null, foreign key(a) references t1(f1) on delete set null) engine=innodb;
show warnings;
drop table t1;
#
# Incorrect types
#
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb;
--error ER_CANT_CREATE_TABLE
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb;
show warnings;
drop table t1;
|
