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
|
--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 1005
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 1005
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 not null primary key, b int) engine=innodb;
--error 1005
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 1005
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 1005
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 1005
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
show warnings;
--error 1005
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 1005
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null;
show warnings;
--error 1005
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 1005
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb;
show warnings;
drop table t1;
|
