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#################################################################
7
8--disable_abort_on_error
9
10# Section x.x.x.1
11# Test case: Verifing that a trigger that activates a primary key results in
12#            the primary key acting correctly on the foreign key
13let $message= Testcase x.x.x.1:;
14--source include/show_msg.inc
15
16
17--disable_warnings
18DROP TABLE IF EXISTS t0, t1, t2;
19--enable_warnings
20
21--replace_result $engine_type <engine_to_be_tested>
22eval
23CREATE TABLE t0 (col1 CHAR(50))
24ENGINE = $engine_type;
25--replace_result $engine_type <engine_to_be_tested>
26eval
27CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
28ENGINE = $engine_type;
29--replace_result $engine_type <engine_to_be_tested>
30eval
31CREATE 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)
34ENGINE = $engine_type;
35
36INSERT INTO t1 VALUES (1,'Department A');
37INSERT INTO t1 VALUES (2,'Department B');
38INSERT INTO t1 VALUES (3,'Department C');
39INSERT INTO t2 VALUES (1,2,'Emp 1');
40INSERT INTO t2 VALUES (2,2,'Emp 2');
41INSERT INTO t2 VALUES (3,2,'Emp 3');
42
43CREATE TRIGGER trig AFTER INSERT ON t0 FOR EACH ROW
44DELETE FROM t1 WHERE col1 = new.col1;
45
46--sorted_result
47SELECT * FROM t2;
48LOCK TABLES t0 WRITE, t1 WRITE;
49INSERT INTO t0 VALUES ('Department B');
50UNLOCK TABLES;
51--sorted_result
52SELECT * FROM t2;
53
54# Cleanup
55DROP TRIGGER trig;
56DROP TABLE t2, t1;
57
58
59#Section x.x.x.2
60# Test case: Checking that triggers can be used as a way to address missing foreign
61#            key definition
62let $message= Testcase x.x.x.2:;
63--source include/show_msg.inc
64
65--disable_warnings
66DROP TABLE IF EXISTS t1, t2;
67--enable_warnings
68
69--replace_result $engine_type <engine_to_be_tested>
70eval
71CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
72ENGINE = $engine_type;
73--replace_result $engine_type <engine_to_be_tested>
74eval
75CREATE 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)
78ENGINE = $engine_type;
79
80INSERT INTO t1 VALUES (1,'Department A');
81INSERT INTO t1 VALUES (2,'Department B');
82INSERT INTO t1 VALUES (3,'Department C');
83INSERT INTO t2 VALUES (1,2,'Emp 1');
84INSERT INTO t2 VALUES (2,3,'Emp 2');
85
86--error ER_NO_REFERENCED_ROW_2
87insert into t2 VALUES (3,4,'Emp 3');
88
89CREATE TRIGGER tr_t2 BEFORE INSERT ON t2 FOR EACH ROW
90INSERT INTO t1 VALUES(new.f_id, CONCAT('New Department ', new.f_id));
91
92LOCK TABLES t1 WRITE, t2 WRITE;
93INSERT INTO t2 VALUES (3,4,'Emp 3');
94UNLOCK TABLES;
95
96--sorted_result
97SELECT * FROM t1;
98--sorted_result
99SELECT * FROM t2;
100
101# Cleanup
102DROP TRIGGER tr_t2;
103DROP TABLE t2, t1, t0;
104
105
106--echo
107--echo Foreign Key tests disabled (bug 11472 - stored in trig_frkey2.test)
108--echo -------------------------------------------------------------------
109