1
#################################################################
2
# This file include tests that address the foreign key cases of
3
# the following requirements since they are specific to innodb.
4
# Other test cases for these requirements are included in the
5
# triggers_*.inc files.
6
#################################################################
8
--disable_abort_on_error
11
# Test case: Verifing that a trigger that activates a primary key results in
12
# the primary key acting correctly on the foreign key
13
let $message= Testcase x.x.x.1:;
14
--source include/show_msg.inc
18
DROP TABLE IF EXISTS t0, t1, t2;
21
--replace_result $engine_type <engine_to_be_tested>
23
CREATE TABLE t0 (col1 CHAR(50))
24
ENGINE = $engine_type;
25
--replace_result $engine_type <engine_to_be_tested>
27
CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
28
ENGINE = $engine_type;
29
--replace_result $engine_type <engine_to_be_tested>
31
CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
32
INDEX par_ind (f_id), col1 CHAR(50),
33
FOREIGN KEY (f_id) REFERENCES t1(id) ON DELETE SET NULL)
34
ENGINE = $engine_type;
36
INSERT INTO t1 VALUES (1,'Department A');
37
INSERT INTO t1 VALUES (2,'Department B');
38
INSERT INTO t1 VALUES (3,'Department C');
39
INSERT INTO t2 VALUES (1,2,'Emp 1');
40
INSERT INTO t2 VALUES (2,2,'Emp 2');
41
INSERT INTO t2 VALUES (3,2,'Emp 3');
43
CREATE TRIGGER trig AFTER INSERT ON t0 FOR EACH ROW
44
DELETE FROM t1 WHERE col1 = new.col1;
48
LOCK TABLES t0 WRITE, t1 WRITE;
49
INSERT INTO t0 VALUES ('Department B');
60
# Test case: Checking that triggers can be used as a way to address missing foreign
62
let $message= Testcase x.x.x.2:;
63
--source include/show_msg.inc
66
DROP TABLE IF EXISTS t1, t2;
69
--replace_result $engine_type <engine_to_be_tested>
71
CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
72
ENGINE = $engine_type;
73
--replace_result $engine_type <engine_to_be_tested>
75
CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
76
INDEX par_ind (f_id), col1 CHAR(50),
77
FOREIGN KEY (f_id) REFERENCES t1(id) ON UPDATE CASCADE)
78
ENGINE = $engine_type;
80
INSERT INTO t1 VALUES (1,'Department A');
81
INSERT INTO t1 VALUES (2,'Department B');
82
INSERT INTO t1 VALUES (3,'Department C');
83
INSERT INTO t2 VALUES (1,2,'Emp 1');
84
INSERT INTO t2 VALUES (2,3,'Emp 2');
86
--error ER_NO_REFERENCED_ROW_2
87
insert into t2 VALUES (3,4,'Emp 3');
89
CREATE TRIGGER tr_t2 BEFORE INSERT ON t2 FOR EACH ROW
90
INSERT INTO t1 VALUES(new.f_id, CONCAT('New Department ', new.f_id));
92
LOCK TABLES t1 WRITE, t2 WRITE;
93
INSERT INTO t2 VALUES (3,4,'Emp 3');
103
DROP TABLE t2, t1, t0;
107
--echo Foreign Key tests disabled (bug 11472 - stored in trig_frkey2.test)
108
--echo -------------------------------------------------------------------