1# 2# Test syntax of foreign keys 3# 4 5-- source include/have_innodb.inc 6 7--disable_warnings 8drop table if exists t1,t2; 9--enable_warnings 10 11create table t1 ( 12 a int not null references t2, 13 b int not null constraint t2_c references t2 (c), 14 primary key (a,b), 15 foreign key (a) references t3 match full, 16 foreign key (a) references t3 match partial, 17 foreign key (a,b) references t3 (c,d) on delete no action 18 on update no action, 19 foreign key (a,b) references t3 (c,d) on update cascade, 20 foreign key (a,b) references t3 (c,d) on delete set default, 21 foreign key (a,b) references t3 (c,d) on update set null); 22 23create index a on t1 (a); 24create unique index b on t1 (a,b); 25drop table t1; 26 27# End of 4.1 tests 28 29# 30# Test DELETE IGNORE 31# Bug#44987 DELETE IGNORE and FK constraint 32# 33 34create table t1 (id int primary key) engine = innodb; 35create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb; 36insert into t1 values (1), (2), (3), (4), (5), (6); 37insert into t2 values (3), (5); 38 39--error 1451 40delete from t1; 41select * from t1; 42 43delete ignore from t1; 44select row_count(); 45select * from t1; 46drop table t2; 47drop table t1; 48 49# 50# Bug#34455 (Ambiguous foreign keys syntax is accepted) 51# 52 53--disable_warnings 54drop table if exists t_34455; 55--enable_warnings 56 57# 2 match clauses, illegal 58--error ER_PARSE_ERROR 59create table t_34455 ( 60 a int not null, 61 foreign key (a) references t3 (a) match full match partial); 62 63# match after on delete, illegal 64--error ER_PARSE_ERROR 65create table t_34455 ( 66 a int not null, 67 foreign key (a) references t3 (a) on delete set default match full); 68 69# match after on update, illegal 70--error ER_PARSE_ERROR 71create table t_34455 ( 72 a int not null, 73 foreign key (a) references t3 (a) on update set default match full); 74 75# 2 on delete clauses, illegal 76--error ER_PARSE_ERROR 77create table t_34455 ( 78 a int not null, 79 foreign key (a) references t3 (a) 80 on delete set default on delete set default); 81 82# 2 on update clauses, illegal 83--error ER_PARSE_ERROR 84create table t_34455 ( 85 a int not null, 86 foreign key (a) references t3 (a) 87 on update set default on update set default); 88 89create table t_34455 (a int not null); 90 91# 2 match clauses, illegal 92--error ER_PARSE_ERROR 93alter table t_34455 94 add foreign key (a) references t3 (a) match full match partial); 95 96# match after on delete, illegal 97--error ER_PARSE_ERROR 98alter table t_34455 99 add foreign key (a) references t3 (a) on delete set default match full); 100 101# match after on update, illegal 102--error ER_PARSE_ERROR 103alter table t_34455 104 add foreign key (a) references t3 (a) on update set default match full); 105 106# 2 on delete clauses, illegal 107--error ER_PARSE_ERROR 108alter table t_34455 109 add foreign key (a) references t3 (a) 110 on delete set default on delete set default); 111 112# 2 on update clauses, illegal 113--error ER_PARSE_ERROR 114alter table t_34455 115 add foreign key (a) references t3 (a) 116 on update set default on update set default); 117 118drop table t_34455; 119 120--echo # 121--echo # MDEV-18460 Don't allow multiple table CONSTRAINTs with the same name. 122--echo # 123 124CREATE TABLE tpk (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL) ENGINE=Innodb; 125--error ER_DUP_CONSTRAINT_NAME 126CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1), CONSTRAINT sid CHECK (c2>15)); 127 128CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1)); 129--error ER_DUP_CONSTRAINT_NAME 130ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15); 131DROP TABLE tfk; 132 133CREATE TABLE tfk (c1 INT, c2 INT, 134 CONSTRAINT sid FOREIGN KEY (c1) REFERENCES tpk (id)) ENGINE=Innodb; 135show create table tfk; 136--error ER_DUP_CONSTRAINT_NAME 137ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15); 138--error ER_DUP_KEYNAME 139ALTER TABLE tfk ADD CONSTRAINT sid UNIQUE(c2); 140DROP TABLE tfk; 141 142DROP TABLE tpk; 143 144 145