#################
# Test RESTRICT #
#################
create table parent(
id int unique key
) engine innodb;
create table child(
parent_id int,
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
on delete restrict
on update restrict
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
delete from parent where id = 1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
delete from child where parent_id = 1;
delete from parent where id = 1;
insert into parent values(1);
insert into child values(1);
update parent set id=id+1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
delete from child;
update parent set id=id+1;
select * from child for system_time from timestamp '1-1-1' to timestamp now(6);
parent_id
1
1
drop table child;
drop table parent;
##############################################
# Test when clustered index is a foreign key #
##############################################
create table parent(
id int(10) unsigned unique key
) engine innodb;
create table child(
parent_id int(10) unsigned primary key,
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
delete from parent where id = 1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
drop table child;
drop table parent;
################
# Test CASCADE #
################
create table parent(
id int unique key
) engine innodb;
create table child(
parent_id int,
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
on delete cascade
on update cascade
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
## FIXME: #415 update of foreign constraints is disabled
call mtr.add_suppression("foreign key constraints in timestamp-based temporal table");
delete from parent where id = 1;
ERROR 42000: Table 'parent' uses an extension that doesn't exist in this MariaDB version
delete from child where parent_id = 1;
## FIXME END
delete from parent where id = 1;
select * from child;
parent_id
select * from child for system_time all;
parent_id
1
insert into parent values(1);
insert into child values(1);
update parent set id = id + 1;
select * from child;
parent_id
2
select * from child for system_time all;
parent_id
1
2
drop table child;
drop table parent;
create or replace table parent (
id int primary key,
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end)
) with system versioning
engine innodb;
create or replace table child (
x int,
parent_id int not null,
constraint `parent-fk`
  foreign key (parent_id) references parent (id)
on delete cascade
on update restrict
)
engine innodb;
insert into parent (id) values (2);
insert into child (x, parent_id) values (2, 2);
delete from parent;
select * from child;
x	parent_id
drop table child;
drop table parent;
create or replace table parent (
id int primary key
)
engine innodb;
create or replace table child (
id int primary key,
parent_id int not null,
constraint `parent-fk`
  foreign key (parent_id) references parent (id)
on delete cascade
on update restrict
) with system versioning
engine innodb;
insert into parent (id) values (3);
insert into child (id, parent_id) values (3, 3);
## FIXME: #415 update of foreign constraints is disabled
delete from child;
## FIXME END
delete from parent;
select * from child;
id	parent_id
select * from child for system_time all;
id	parent_id
3	3
drop table child;
drop table parent;
#################
# Test SET NULL #
#################
create table parent(
id int unique key
) engine innodb;
create table child(
parent_id int,
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end),
foreign key(parent_id) references parent(id)
on delete set null
on update set null
) engine innodb with system versioning;
insert into parent values(1);
insert into child values(1);
delete from child;
insert into child values(1);
## FIXME: #415 update of foreign constraints is disabled
delete from child where parent_id = 1;
## FIXME END
delete from parent where id = 1;
select * from child;
parent_id
select * from child for system_time from timestamp '1-1-1' to timestamp now(6);
parent_id
1
1
delete from child;
insert into parent values(1);
insert into child values(1);
drop table child;
drop table parent;
###########################
# Parent table is foreign #
###########################
create or replace table parent(
id int unique key,
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end)
) engine innodb with system versioning;
create or replace table child(
parent_id int,
foreign key(parent_id) references parent(id)
) engine innodb;
insert into parent values(1);
insert into child values(1);
delete from parent;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
update parent set id=2;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
delete from child;
delete from parent;
insert into child values(1);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
insert into parent values(1);
insert into child values(1);
delete from parent;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
update parent set id=2;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
drop table child;
drop table parent;
###################
# crash on DELETE #
###################
create or replace table a (
cola int(10) primary key,
v_cola int(10) as (cola mod 10) virtual,
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end)
) engine=innodb with system versioning;
create index v_cola on a (v_cola);
create or replace table b(
cola int(10),
v_cola int(10),
sys_start SYS_DATATYPE as row start invisible,
sys_end SYS_DATATYPE as row end invisible,
period for system_time(sys_start, sys_end)
) engine=innodb with system versioning;
alter table b add constraint `v_cola_fk`
foreign key (v_cola) references a (v_cola);
insert into a(cola) values (12);
insert into b(cola, v_cola) values (10,2);
delete from a;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`))
drop table b, a;