Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_trig_frkey.result

raw
1
2 Testcase x.x.x.1:
3 -----------------
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;
21 SELECT * FROM t2;
22 id f_id col1
23 1 2 Emp 1
24 2 2 Emp 2
25 3 2 Emp 3
26 LOCK TABLES t0 WRITE, t1 WRITE;
27 INSERT INTO t0 VALUES ('Department B');
28 UNLOCK TABLES;
29 SELECT * FROM t2;
30 id f_id col1
31 1 NULL Emp 1
32 2 NULL Emp 2
33 3 NULL Emp 3
34 DROP TRIGGER trig;
35 DROP TABLE t2, t1;
36
37 Testcase x.x.x.2:
38 -----------------
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');
57 UNLOCK TABLES;
58 SELECT * FROM t1;
59 id col1
60 1 Department A
61 2 Department B
62 3 Department C
63 4 New Department 4
64 SELECT * FROM t2;
65 id f_id col1
66 1 2 Emp 1
67 2 3 Emp 2
68 3 4 Emp 3
69 DROP TRIGGER tr_t2;
70 DROP TABLE t2, t1, t0;
71
72 Foreign Key tests disabled (bug 11472 - stored in trig_frkey2.test)
73 -------------------------------------------------------------------