Debian Codegraph @

mysql-test/suite/innodb/t/innodb-fk-warnings.test

raw
1 --source include/have_innodb.inc
2
3 #
4 # MDEV-8524: Improve error messaging when there is duplicate key or foreign key names
5 #
6 CREATE TABLE t1 (
7 id int(11) NOT NULL PRIMARY KEY,
8 a int(11) NOT NULL,
9 b int(11) NOT NULL,
10 c int not null,
11 CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
12 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
13
14 #
15 # Below create table fails because constraint name test
16 # is reserved for above table.
17 #
18 --error ER_CANT_CREATE_TABLE
19 CREATE TABLE t2 (
20 id int(11) NOT NULL PRIMARY KEY,
21 a int(11) NOT NULL,
22 b int(11) NOT NULL,
23 c int not null,
24 CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id),
25 CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
26 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
27
28 show warnings;
29
30 drop table t1;
31
32 #
33 # MDEV-6697: Improve foreign keys warnings/errors
34 #
35
36 #
37 # No index for referenced columns
38 #
39 create table t1(a int) engine=innodb;
40 --error ER_CANT_CREATE_TABLE
41 create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=innodb;
42 show warnings;
43 drop table t1;
44
45 create table t1(a int unique, b int) engine=innodb;
46 --error ER_CANT_CREATE_TABLE
47 create table t2(a int, b int, foreign key (a) references t1(a), foreign key (b) references t1(b)) engine=innodb;
48 show warnings;
49 drop table t1;
50
51 create table t1(a int not null primary key, b int) engine=innodb;
52 --error ER_CANT_CREATE_TABLE
53 create table t2(a int, b int, constraint a foreign key a (a) references t1(a),
54 constraint a foreign key a (a) references t1(b)) engine=innodb;
55 show warnings;
56 create table t2(a int, b int, constraint a foreign key a (a) references t1(a)) engine=innodb;
57 --error ER_CANT_CREATE_TABLE
58 alter table t2 add constraint b foreign key (b) references t2(b);
59 show warnings;
60 drop table t2, t1;
61
62 #
63 # Referenced table does not exists
64 #
65
66 create table t1 (f1 integer primary key) engine=innodb;
67 --error ER_CANT_CREATE_TABLE
68 alter table t1 add constraint c1 foreign key (f1) references t11(f1);
69 show warnings;
70 drop table t1;
71
72 #
73 # Foreign key on temporal tables
74 #
75
76 create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb;
77 # remove echos and uncomment the commented when MDEV-8569 is fixed
78 --echo create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
79 --echo ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
80 --echo show warnings;
81 --echo Level Code Message
82 --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.
83 --echo Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
84 --echo Warning 1215 Cannot add foreign key constraint
85 --echo alter table t1 add foreign key(b) references t1(a);
86 --echo ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
87 --echo show warnings;
88 --echo Level Code Message
89 --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).
90 --echo Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
91 --echo Warning 1215 Cannot add foreign key constraint
92 --error ER_CANT_CREATE_TABLE
93 create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
94 show warnings;
95 --error ER_CANT_CREATE_TABLE
96 alter table t1 add foreign key(b) references t1(a);
97 show warnings;
98 drop table t1;
99
100 #
101 # Column numbers do not match
102 #
103 create table t1(a int not null primary key, b int, key(b)) engine=innodb;
104 --error 1239
105 alter table t1 add foreign key(a,b) references t1(a);
106 show warnings;
107 drop table t1;
108 create table t1(a int not null primary key, b int, key(b)) engine=innodb;
109 --error 1239
110 alter table t1 add foreign key(a) references t1(a,b);
111 show warnings;
112 drop table t1;
113
114 #
115 # ON UPDATE/DELETE SET NULL on NOT NULL column
116 #
117 create table t1 (f1 integer not null primary key) engine=innodb;
118 --error ER_CANT_CREATE_TABLE
119 alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null;
120 show warnings;
121 --error ER_CANT_CREATE_TABLE
122 create table t2(a int not null, foreign key(a) references t1(f1) on delete set null) engine=innodb;
123 show warnings;
124 drop table t1;
125
126 #
127 # Incorrect types
128 #
129 create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb;
130 --error ER_CANT_CREATE_TABLE
131 create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb;
132 show warnings;
133 drop table t1;