4
DROP TABLE IF EXISTS t0, t1, t2;
5
CREATE TABLE t0 (col1 CHAR(50))
6
ENGINE = <engine_to_be_tested>;
7
CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
8
ENGINE = <engine_to_be_tested>;
9
CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
10
INDEX par_ind (f_id), col1 CHAR(50),
11
FOREIGN KEY (f_id) REFERENCES t1(id) ON DELETE SET NULL)
12
ENGINE = <engine_to_be_tested>;
13
INSERT INTO t1 VALUES (1,'Department A');
14
INSERT INTO t1 VALUES (2,'Department B');
15
INSERT INTO t1 VALUES (3,'Department C');
16
INSERT INTO t2 VALUES (1,2,'Emp 1');
17
INSERT INTO t2 VALUES (2,2,'Emp 2');
18
INSERT INTO t2 VALUES (3,2,'Emp 3');
19
CREATE TRIGGER trig AFTER INSERT ON t0 FOR EACH ROW
20
DELETE FROM t1 WHERE col1 = new.col1;
26
LOCK TABLES t0 WRITE, t1 WRITE;
27
INSERT INTO t0 VALUES ('Department B');
39
DROP TABLE IF EXISTS t1, t2;
40
CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
41
ENGINE = <engine_to_be_tested>;
42
CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
43
INDEX par_ind (f_id), col1 CHAR(50),
44
FOREIGN KEY (f_id) REFERENCES t1(id) ON UPDATE CASCADE)
45
ENGINE = <engine_to_be_tested>;
46
INSERT INTO t1 VALUES (1,'Department A');
47
INSERT INTO t1 VALUES (2,'Department B');
48
INSERT INTO t1 VALUES (3,'Department C');
49
INSERT INTO t2 VALUES (1,2,'Emp 1');
50
INSERT INTO t2 VALUES (2,3,'Emp 2');
51
insert into t2 VALUES (3,4,'Emp 3');
52
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `t1` (`id`) ON UPDATE CASCADE)
53
CREATE TRIGGER tr_t2 BEFORE INSERT ON t2 FOR EACH ROW
54
INSERT INTO t1 VALUES(new.f_id, CONCAT('New Department ', new.f_id));
55
LOCK TABLES t1 WRITE, t2 WRITE;
56
INSERT INTO t2 VALUES (3,4,'Emp 3');
70
DROP TABLE t2, t1, t0;
72
Foreign Key tests disabled (bug 11472 - stored in trig_frkey2.test)
73
-------------------------------------------------------------------