1--source include/have_innodb.inc 2--source include/not_embedded.inc 3 4--echo # 5--echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW 6--echo # DICT_CREATE_FOREIGN_CONSTR 7--echo # 8 9create table t1 (f1 int primary key) engine=InnoDB; 10--error ER_CANNOT_ADD_FOREIGN 11create table t2 (f1 int primary key, 12constraint c1 foreign key (f1) references t1(f1), 13constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB; 14create table t2 (f1 int primary key, 15 constraint c1 foreign key (f1) references t1(f1)) engine=innodb; 16 17--replace_regex /#sql-[0-9a-f_]*'/#sql-temporary'/ 18--error ER_DUP_KEY 19alter table t2 add constraint c1 foreign key (f1) references t1(f1); 20 21set foreign_key_checks = 0; 22--error ER_FK_DUP_NAME 23alter table t2 add constraint c1 foreign key (f1) references t1(f1); 24 25drop table t2, t1; 26 27--echo # 28--echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN 29--echo # NULL/NOT NULL MISMATCH 30--echo # 31 32set foreign_key_checks = 1; 33show variables like 'foreign_key_checks'; 34 35CREATE TABLE t1 36(a INT NOT NULL, 37 b INT NOT NULL, 38 INDEX idx(a)) ENGINE=InnoDB; 39 40CREATE TABLE t2 41(a INT KEY, 42 b INT, 43 INDEX ind(b), 44 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) 45 ENGINE=InnoDB; 46 47show create table t1; 48show create table t2; 49 50INSERT INTO t1 VALUES (1, 80); 51INSERT INTO t1 VALUES (2, 81); 52INSERT INTO t1 VALUES (3, 82); 53INSERT INTO t1 VALUES (4, 83); 54INSERT INTO t1 VALUES (5, 84); 55 56INSERT INTO t2 VALUES (51, 1); 57INSERT INTO t2 VALUES (52, 2); 58INSERT INTO t2 VALUES (53, 3); 59INSERT INTO t2 VALUES (54, 4); 60INSERT INTO t2 VALUES (55, 5); 61 62SELECT a, b FROM t1 ORDER BY a; 63SELECT a, b FROM t2 ORDER BY a; 64 65--error ER_NO_REFERENCED_ROW_2 66INSERT INTO t2 VALUES (56, 6); 67 68ALTER TABLE t1 CHANGE a id INT; 69 70SELECT id, b FROM t1 ORDER BY id; 71SELECT a, b FROM t2 ORDER BY a; 72 73--echo # Operations on child table 74--error ER_NO_REFERENCED_ROW_2 75INSERT INTO t2 VALUES (56, 6); 76--error ER_NO_REFERENCED_ROW_2 77UPDATE t2 SET b = 99 WHERE a = 51; 78DELETE FROM t2 WHERE a = 53; 79SELECT id, b FROM t1 ORDER BY id; 80SELECT a, b FROM t2 ORDER BY a; 81 82--echo # Operations on parent table 83DELETE FROM t1 WHERE id = 1; 84UPDATE t1 SET id = 50 WHERE id = 5; 85SELECT id, b FROM t1 ORDER BY id; 86SELECT a, b FROM t2 ORDER BY a; 87 88DROP TABLE t2, t1; 89 90--echo # 91--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART 92--echo # base bug#24818604 [GR] 93--echo # 94 95CREATE TABLE t1 (c1 INT PRIMARY KEY); 96CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1)); 97 98INSERT INTO t1 VALUES (1); 99INSERT INTO t2 VALUES (1); 100 101SELECT unique_constraint_name FROM information_schema.referential_constraints 102WHERE table_name = 't2'; 103 104--source include/restart_mysqld.inc 105 106SELECT unique_constraint_name FROM information_schema.referential_constraints 107WHERE table_name = 't2'; 108 109SELECT * FROM t1; 110 111SELECT unique_constraint_name FROM information_schema.referential_constraints 112WHERE table_name = 't2'; 113 114DROP TABLE t2; 115DROP TABLE t1; 116