1 files changed, 282 insertions, 0 deletions
diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test
new file mode 100644
index 00000000000..81d1caa2594
--- /dev/null
+++ b/mysql-test/suite/versioning/t/foreign.test
@@ -0,0 +1,282 @@
+--source suite/versioning/common.inc
+
+--echo #################
+--echo # Test RESTRICT #
+--echo #################
+
+create table parent(
+  id int unique key
+) engine innodb;
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create table child(
+  parent_id int,
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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);
+
+-- error ER_ROW_IS_REFERENCED_2
+delete from parent where id = 1;
+delete from child where parent_id = 1;
+delete from parent where id = 1;
+
+insert into parent values(1);
+insert into child values(1);
+-- error ER_ROW_IS_REFERENCED_2
+update parent set id=id+1;
+delete from child;
+update parent set id=id+1;
+select * from child for system_time from timestamp '1-1-1' to timestamp now(6);
+
+drop table child;
+drop table parent;
+
+--echo ##############################################
+--echo # Test when clustered index is a foreign key #
+--echo ##############################################
+
+create table parent(
+  id int(10) unsigned unique key
+) engine innodb;
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create table child(
+  parent_id int(10) unsigned primary key,
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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);
+
+-- error ER_ROW_IS_REFERENCED_2
+delete from parent where id = 1;
+
+drop table child;
+drop table parent;
+
+--echo ################
+--echo # Test CASCADE #
+--echo ################
+
+create table parent(
+  id int unique key
+) engine innodb;
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create table child(
+  parent_id int,
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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);
+
+--echo ## FIXME: #415 update of foreign constraints is disabled
+call mtr.add_suppression("foreign key constraints in timestamp-based temporal table");
+--error ER_UNSUPPORTED_EXTENSION
+delete from parent where id = 1;
+delete from child where parent_id = 1;
+--echo ## FIXME END
+delete from parent where id = 1;
+select * from child;
+select * from child for system_time all;
+
+insert into parent values(1);
+insert into child values(1);
+update parent set id = id + 1;
+select * from child;
+select * from child for system_time all;
+
+drop table child;
+drop table parent;
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create or replace table parent (
+  id int primary key,
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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;
+
+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);
+--echo ## FIXME: #415 update of foreign constraints is disabled
+delete from child;
+--echo ## FIXME END
+delete from parent;
+select * from child;
+select * from child for system_time all;
+
+drop table child;
+drop table parent;
+
+--echo #################
+--echo # Test SET NULL #
+--echo #################
+
+create table parent(
+  id int unique key
+) engine innodb;
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create table child(
+  parent_id int,
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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);
+
+--echo ## FIXME: #415 update of foreign constraints is disabled
+delete from child where parent_id = 1;
+--echo ## FIXME END
+delete from parent where id = 1;
+select * from child;
+select * from child for system_time from timestamp '1-1-1' to timestamp now(6);
+delete from child;
+
+insert into parent values(1);
+insert into child values(1);
+## FIXME: #415 update of foreign constraints is disabled
+if (0)
+{
+update parent set id=id+1;
+select * from child;
+select * from child for system_time from timestamp '1-1-1' to timestamp now(6);
+}
+## FIXME END
+
+drop table child;
+drop table parent;
+
+--echo ###########################
+--echo # Parent table is foreign #
+--echo ###########################
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create or replace table parent(
+  id int unique key,
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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);
+-- error ER_ROW_IS_REFERENCED_2
+delete from parent;
+-- error ER_ROW_IS_REFERENCED_2
+update parent set id=2;
+
+delete from child;
+delete from parent;
+
+-- error ER_NO_REFERENCED_ROW_2
+insert into child values(1);
+
+insert into parent values(1);
+insert into child values(1);
+-- error ER_ROW_IS_REFERENCED_2
+delete from parent;
+-- error ER_ROW_IS_REFERENCED_2
+update parent set id=2;
+
+drop table child;
+drop table parent;
+
+--echo ###################
+--echo # crash on DELETE #
+--echo ###################
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create or replace table a (
+  cola int(10) primary key,
+  v_cola int(10) as (cola mod 10) virtual,
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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);
+
+--replace_result $sys_datatype_expl SYS_DATATYPE
+eval create or replace table b(
+  cola int(10),
+  v_cola int(10),
+  sys_start $sys_datatype_expl as row start invisible,
+  sys_end $sys_datatype_expl 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);
+--error ER_ROW_IS_REFERENCED_2
+delete from a;
+
+drop table b, a;
+
+--source suite/versioning/common_finish.inc