diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb-fk-warnings.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-fk-warnings.test | 133 |
1 files changed, 133 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-fk-warnings.test b/mysql-test/suite/innodb/t/innodb-fk-warnings.test new file mode 100644 index 00000000..8a0ed581 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-fk-warnings.test @@ -0,0 +1,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; |