Debian Codegraph @

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

raw
1 Warnings:
2 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
3
4 Testcase x.x.x.1:
5 -----------------
6 Warnings:
7 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
8 DROP TABLE IF EXISTS t0, t1, t2;
9 CREATE TABLE t0 (col1 CHAR(50))
10 ENGINE = <engine_to_be_tested>;
11 CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
12 ENGINE = <engine_to_be_tested>;
13 CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
14 INDEX par_ind (f_id), col1 CHAR(50),
15 FOREIGN KEY (f_id) REFERENCES t1(id) ON DELETE SET NULL)
16 ENGINE = <engine_to_be_tested>;
17 INSERT INTO t1 VALUES (1,'Department A');
18 INSERT INTO t1 VALUES (2,'Department B');
19 INSERT INTO t1 VALUES (3,'Department C');
20 INSERT INTO t2 VALUES (1,2,'Emp 1');
21 INSERT INTO t2 VALUES (2,2,'Emp 2');
22 INSERT INTO t2 VALUES (3,2,'Emp 3');
23 CREATE TRIGGER trig AFTER INSERT ON t0 FOR EACH ROW
24 DELETE FROM t1 WHERE col1 = new.col1;
25 SELECT * FROM t2;
26 id f_id col1
27 1 2 Emp 1
28 2 2 Emp 2
29 3 2 Emp 3
30 LOCK TABLES t0 WRITE, t1 WRITE;
31 INSERT INTO t0 VALUES ('Department B');
32 UNLOCK TABLES;
33 SELECT * FROM t2;
34 id f_id col1
35 1 NULL Emp 1
36 2 NULL Emp 2
37 3 NULL Emp 3
38 DROP TRIGGER trig;
39 DROP TABLE t2, t1;
40 Warnings:
41 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
42
43 Testcase x.x.x.2:
44 -----------------
45 Warnings:
46 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
47 DROP TABLE IF EXISTS t1, t2;
48 CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id))
49 ENGINE = <engine_to_be_tested>;
50 CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT,
51 INDEX par_ind (f_id), col1 CHAR(50),
52 FOREIGN KEY (f_id) REFERENCES t1(id) ON UPDATE CASCADE)
53 ENGINE = <engine_to_be_tested>;
54 INSERT INTO t1 VALUES (1,'Department A');
55 INSERT INTO t1 VALUES (2,'Department B');
56 INSERT INTO t1 VALUES (3,'Department C');
57 INSERT INTO t2 VALUES (1,2,'Emp 1');
58 INSERT INTO t2 VALUES (2,3,'Emp 2');
59 insert into t2 VALUES (3,4,'Emp 3');
60 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)
61 CREATE TRIGGER tr_t2 BEFORE INSERT ON t2 FOR EACH ROW
62 INSERT INTO t1 VALUES(new.f_id, CONCAT('New Department ', new.f_id));
63 LOCK TABLES t1 WRITE, t2 WRITE;
64 INSERT INTO t2 VALUES (3,4,'Emp 3');
65 UNLOCK TABLES;
66 SELECT * FROM t1;
67 id col1
68 1 Department A
69 2 Department B
70 3 Department C
71 4 New Department 4
72 SELECT * FROM t2;
73 id f_id col1
74 1 2 Emp 1
75 2 3 Emp 2
76 3 4 Emp 3
77 DROP TRIGGER tr_t2;
78 DROP TABLE t2, t1, t0;
79
80 Foreign Key tests disabled (bug 11472 - stored in trig_frkey2.test)
81 -------------------------------------------------------------------