From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/suite/innodb/r/innodb-fk-virtual.result | 86 ++++++++++++++++++++++ 1 file changed, 86 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb-fk-virtual.result (limited to 'mysql-test/suite/innodb/r/innodb-fk-virtual.result') diff --git a/mysql-test/suite/innodb/r/innodb-fk-virtual.result b/mysql-test/suite/innodb/r/innodb-fk-virtual.result new file mode 100644 index 00000000..b39e770c --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-fk-virtual.result @@ -0,0 +1,86 @@ +create or replace table a ( +cola int(10) primary key, +v_cola int(10) as (cola mod 10) virtual, +p_cola int(10) as (cola mod 10) persistent +) engine=innodb; +create index v_cola on a (v_cola); +create index p_cola on a (p_cola); +create or replace table b( +cola int(10), +v_cola int(10), +p_cola int(10), +c_cola int(10) as (cola + 2) virtual +) engine=innodb; +alter table b add constraint `p_cola_fk` +foreign key (p_cola) references a (p_cola) +on delete restrict +on update restrict; +show warnings; +Level Code Message +show create table b; +Table Create Table +b CREATE TABLE `b` ( + `cola` int(10) DEFAULT NULL, + `v_cola` int(10) DEFAULT NULL, + `p_cola` int(10) DEFAULT NULL, + `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL, + KEY `p_cola_fk` (`p_cola`), + CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +alter table b add constraint `v_cola_fk` +foreign key (v_cola) references a (v_cola) +on delete restrict +on update restrict; +show warnings; +Level Code Message +show create table b; +Table Create Table +b CREATE TABLE `b` ( + `cola` int(10) DEFAULT NULL, + `v_cola` int(10) DEFAULT NULL, + `p_cola` int(10) DEFAULT NULL, + `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL, + KEY `p_cola_fk` (`p_cola`), + KEY `v_cola_fk` (`v_cola`), + CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`), + CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +alter table b add constraint `c_cola_fk` +foreign key (c_cola) references a (cola) +on delete restrict +on update restrict; +show warnings; +Level Code Message +show create table b; +Table Create Table +b CREATE TABLE `b` ( + `cola` int(10) DEFAULT NULL, + `v_cola` int(10) DEFAULT NULL, + `p_cola` int(10) DEFAULT NULL, + `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL, + KEY `p_cola_fk` (`p_cola`), + KEY `v_cola_fk` (`v_cola`), + KEY `c_cola_fk` (`c_cola`), + CONSTRAINT `c_cola_fk` FOREIGN KEY (`c_cola`) REFERENCES `a` (`cola`), + CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`), + CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +insert into a(cola) values (12); +select * from a; +cola v_cola p_cola +12 2 2 +insert into b(cola, v_cola, p_cola) values (10,2,2); +select * from b; +cola v_cola p_cola c_cola +10 2 2 12 +insert into b(cola, v_cola, p_cola) values (10,1,1); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`)) +delete from a; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `c_cola_fk` FOREIGN KEY (`c_cola`) REFERENCES `a` (`cola`)) +select * from a; +cola v_cola p_cola +12 2 2 +select * from b; +cola v_cola p_cola c_cola +10 2 2 12 +drop table b, a; -- cgit v1.2.3