| 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; |