Edit File: innodb_trig_frkey.result
Testcase x.x.x.1: ----------------- DROP TABLE IF EXISTS t0, t1, t2; CREATE TABLE t0 (col1 CHAR(50)) ENGINE = <engine_to_be_tested>; CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id)) ENGINE = <engine_to_be_tested>; CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, INDEX par_ind (f_id), col1 CHAR(50), FOREIGN KEY (f_id) REFERENCES t1(id) ON DELETE SET NULL) ENGINE = <engine_to_be_tested>; INSERT INTO t1 VALUES (1,'Department A'); INSERT INTO t1 VALUES (2,'Department B'); INSERT INTO t1 VALUES (3,'Department C'); INSERT INTO t2 VALUES (1,2,'Emp 1'); INSERT INTO t2 VALUES (2,2,'Emp 2'); INSERT INTO t2 VALUES (3,2,'Emp 3'); CREATE TRIGGER trig AFTER INSERT ON t0 FOR EACH ROW DELETE FROM t1 WHERE col1 = new.col1; SELECT * FROM t2; id f_id col1 1 2 Emp 1 2 2 Emp 2 3 2 Emp 3 LOCK TABLES t0 WRITE, t1 WRITE; INSERT INTO t0 VALUES ('Department B'); UNLOCK TABLES; SELECT * FROM t2; id f_id col1 1 NULL Emp 1 2 NULL Emp 2 3 NULL Emp 3 DROP TRIGGER trig; DROP TABLE t2, t1; Testcase x.x.x.2: ----------------- DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (id INT NOT NULL, col1 CHAR(50), PRIMARY KEY (id)) ENGINE = <engine_to_be_tested>; CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, INDEX par_ind (f_id), col1 CHAR(50), FOREIGN KEY (f_id) REFERENCES t1(id) ON UPDATE CASCADE) ENGINE = <engine_to_be_tested>; INSERT INTO t1 VALUES (1,'Department A'); INSERT INTO t1 VALUES (2,'Department B'); INSERT INTO t1 VALUES (3,'Department C'); INSERT INTO t2 VALUES (1,2,'Emp 1'); INSERT INTO t2 VALUES (2,3,'Emp 2'); insert into t2 VALUES (3,4,'Emp 3'); 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) CREATE TRIGGER tr_t2 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t1 VALUES(new.f_id, CONCAT('New Department ', new.f_id)); LOCK TABLES t1 WRITE, t2 WRITE; INSERT INTO t2 VALUES (3,4,'Emp 3'); UNLOCK TABLES; SELECT * FROM t1; id col1 1 Department A 2 Department B 3 Department C 4 New Department 4 SELECT * FROM t2; id f_id col1 1 2 Emp 1 2 3 Emp 2 3 4 Emp 3 DROP TRIGGER tr_t2; DROP TABLE t2, t1, t0; Foreign Key tests disabled (bug 11472 - stored in trig_frkey2.test) -------------------------------------------------------------------
Back to File Manager