diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/foreign_key.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/foreign_key.result | 100 |
1 files changed, 95 insertions, 5 deletions
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index 4068551ec44..00af01d9501 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -237,11 +237,7 @@ SET SESSION FOREIGN_KEY_CHECKS = OFF; ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x); SET SESSION FOREIGN_KEY_CHECKS = ON; ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f); -Warnings: -Warning 1088 failed to load FOREIGN KEY constraints ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f); -Warnings: -Warning 1088 failed to load FOREIGN KEY constraints DROP TABLE t1; CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY) ENGINE=InnoDB; @@ -262,7 +258,7 @@ ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") SHOW WARNINGS; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t2` not found in the data dictionary near 'FOREIGN KEY (a) REFERENCES t2 (b)'. +Warning 150 Alter table `test`.`t1` with foreign key (a) constraint failed. Referenced table `test`.`t2` not found in the data dictionary. Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") Warning 1215 Cannot add foreign key constraint for `t1` DROP TABLE t1; @@ -849,3 +845,97 @@ INSERT INTO t1 VALUES (2,11,11); DROP TABLE t1; SET FOREIGN_KEY_CHECKS=DEFAULT; # End of 10.4 tests +# +# MDEV-20729 Fix REFERENCES constraint in column definition +# +set default_storage_engine= innodb; +create table t1 (x int primary key, y int unique); +create table t2 (x int references t1(x), y int constraint fk references t1(y)); +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `x` int(11) DEFAULT NULL, + `y` int(11) DEFAULT NULL, + KEY `x` (`x`), + KEY `fk` (`y`), + CONSTRAINT `fk` FOREIGN KEY (`y`) REFERENCES `t1` (`y`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +create table t3 (z int); +alter table t3 add x int references t1(x), add y int constraint fk2 references t1(y); +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `z` int(11) DEFAULT NULL, + `x` int(11) DEFAULT NULL, + `y` int(11) DEFAULT NULL, + KEY `x` (`x`), + KEY `fk2` (`y`), + CONSTRAINT `fk2` FOREIGN KEY (`y`) REFERENCES `t1` (`y`), + CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop tables t3, t2, t1; +create table t1 (id int primary key); +create table t2 (id2 int references t1); +ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +create table t2 (id int references t1); +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) DEFAULT NULL, + KEY `id` (`id`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop tables t2, t1; +set default_storage_engine= default; +# +# MDEV-21690 LeakSanitizer: detected memory leaks in mem_heap_create_block_func +# +SET FOREIGN_KEY_CHECKS=1; +CREATE TABLE t1 (a TEXT, b TEXT) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b); +ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +SET FOREIGN_KEY_CHECKS=DEFAULT; +DROP TABLE t1; +# +# MDEV-22602 Disable UPDATE CASCADE for SQL constraints +# +# TODO: enable them after MDEV-16417 is finished +create or replace table t1 (a int primary key) engine=innodb; +create or replace table t2 (a int, check(a > 0), foreign key(a) references t1(a) on update cascade) engine=innodb; +ERROR HY000: Function or expression 'a' cannot be used in the CHECK clause of `CONSTRAINT_1` +create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=innodb; +create or replace table t2 ( +a int, s date, e date, +period for p (s, e), +primary key (a, p without overlaps), +foreign key (a, e, s) references t1 (f1, f3, f2) on delete cascade on update cascade) engine=innodb; +ERROR HY000: Key `PRIMARY` cannot have WITHOUT OVERLAPS +create or replace table t1 (a varchar(4096) unique) engine=innodb; +create or replace table t2 (pk int primary key, a varchar(4096) unique, foreign key(a) references t1(a) on update cascade) engine=innodb; +ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +drop table t1; +# +# MDEV-26824 Can't add foreign key with empty referenced columns list +# +create table t2(a int primary key) engine=innodb; +create table t1(a int primary key, b int) engine=innodb; +alter table t2 add foreign key(a) references t1(a, b); +ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match +create or replace table t1(a tinyint primary key) engine innodb; +alter table t2 add foreign key(a) references t1; +ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +create or replace table t1(b int primary key) engine innodb; +alter table t2 add foreign key(a) references t1; +ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +create or replace table t1(a int primary key, b int) engine innodb; +alter table t2 add foreign key(a) references t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop tables t2, t1; +# End of 10.5 tests |