mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
86 lines
2.9 KiB
Text
86 lines
2.9 KiB
Text
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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;
|