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