Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 144 insertions, 0 deletions

diff --git a/mysql-test/main/foreign_key.test b/mysql-test/main/foreign_key.test
new file mode 100644
index 00000000..8f42d437
--- /dev/null
+++ b/mysql-test/main/foreign_key.test
@@ -0,0 +1,144 @@
+#
+# Test syntax of foreign keys
+#
+
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+create table t1 (
+	a int not null references t2,
+	b int not null constraint t2_c references t2 (c),
+	primary key (a,b),
+	foreign key (a) references t3 match full,
+	foreign key (a) references t3 match partial,
+	foreign key (a,b) references t3 (c,d) on delete no action
+	  on update no action,
+	foreign key (a,b) references t3 (c,d) on update cascade,
+	foreign key (a,b) references t3 (c,d) on delete set default,
+	foreign key (a,b) references t3 (c,d) on update set null);
+
+create index a on t1 (a);
+create unique index b on t1 (a,b);
+drop table t1;
+
+# End of 4.1 tests
+
+#
+# Test DELETE IGNORE
+# Bug#44987 DELETE IGNORE and FK constraint
+#
+
+create table t1 (id int primary key) engine = innodb;
+create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb;
+insert into t1 values (1), (2), (3), (4), (5), (6);
+insert into t2 values (3), (5);
+
+--error 1451
+delete from t1;
+select * from t1;
+
+delete ignore from t1;
+select row_count();
+select * from t1;
+drop table t2;
+drop table t1;
+
+#
+# Bug#34455 (Ambiguous foreign keys syntax is accepted)
+#
+
+--disable_warnings
+drop table if exists t_34455;
+--enable_warnings
+
+# 2 match clauses, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a) match full match partial);
+
+# match after on delete, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a) on delete set default match full);
+
+# match after on update, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a) on update set default match full);
+
+# 2 on delete clauses, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a)
+  on delete set default on delete set default);
+
+# 2 on update clauses, illegal
+--error ER_PARSE_ERROR
+create table t_34455 (
+  a int not null,
+  foreign key (a) references t3 (a)
+  on update set default on update set default);
+
+create table t_34455 (a int not null);
+
+# 2 match clauses, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a) match full match partial);
+
+# match after on delete, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a) on delete set default match full);
+
+# match after on update, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a) on update set default match full);
+
+# 2 on delete clauses, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a)
+  on delete set default on delete set default);
+
+# 2 on update clauses, illegal
+--error ER_PARSE_ERROR
+alter table t_34455
+  add foreign key (a) references t3 (a)
+  on update set default on update set default);
+
+drop table t_34455;
+
+--echo #
+--echo # MDEV-18460 Don't allow multiple table CONSTRAINTs with the same name.
+--echo #
+
+CREATE TABLE tpk (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL) ENGINE=Innodb;
+--error ER_DUP_CONSTRAINT_NAME
+CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1), CONSTRAINT sid CHECK (c2>15));
+
+CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1));
+--error ER_DUP_CONSTRAINT_NAME
+ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15);
+DROP TABLE tfk;
+
+CREATE TABLE tfk (c1 INT, c2 INT,
+  CONSTRAINT sid FOREIGN KEY (c1) REFERENCES tpk (id)) ENGINE=Innodb;
+show create table tfk;
+--error ER_DUP_CONSTRAINT_NAME
+ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15);
+--error ER_DUP_KEYNAME
+ALTER TABLE tfk ADD CONSTRAINT sid UNIQUE(c2);
+DROP TABLE tfk;
+
+DROP TABLE tpk;
+
+