Nikita Malyavin added a comment - 2018-09-09 19:20 - edited Some notes from sql standard:  If there is more than one referencing column, at least one of which is nullable, and if  <referencing period specification> is specified, then let CATPN be the  <application time period name> contained in <referencing period specification>  and let PATPN be the <application time period name> contained in the  <referenced period specification>; the various <match type>s have the following semantics:  — MATCH SIMPLE: if at least one referencing column is null, then the row of the referencing table passes the constraint check. If all referencing columns are not null and the referenced table is not a system-versioned table, then a row R of the referencing table passes the constraint check if and only if there is a non-empty set S of rows of the referenced table such that every row in S matches all the referencing columns of R and the CATPN period value of R is a subset of the union of the PATPN period values of the rows in S. If all referencing columns are not null and the referenced table is a system-versioned table, then a row R of the referencing table passes the constraint check if and only if there is a non-empty set S of current system rows of the referenced table such that every row in S matches all the referencing columns of R and the CATPN period value of R is a subset of the union of the PATPN period values of the rows in S. — MATCH PARTIAL: if all referencing columns are null, then the row of the referencing table passes the constraint check. If at least one referencing column is not null and the referenced table is not a system-versioned table, then a row R of the referencing table passes the constraint check if and only if there is a non-empty set S of rows of the referenced table such that every row in S matches all the non-null referencing columns of R and the CATPN period value of R is a subset of the union of the PATPN period values of the rows in S. If at least one referencing columns is not null and the referenced table is a system-versioned table, then a row R of the referencing table passes the constraint check if and only if there is a non-empty set S of current system rows of the referenced table such that every row in S matches all the non-null referencing columns of R and the CATPN period value of R is a subset of the union of the PATPN period values of the rows in S. — MATCH FULL: if all referencing columns are null, then the row of the referencing table passes the constraint check. If all referencing columns are not null and the referenced table is not a system-versioned table, then a row R of the referencing table passes the constraint check if and only if there is a non-empty set S of rows of the referenced table such that every row in S matches all the referencing columns of R and the CATPN period value of R is a subset of the union of the PATPN period values of the rows in S. If all referencing columns are not null and the referenced table is a system-versioned table, then a row R of the referencing table passes the constraint check if and only if there is a non-empty set S of current system rows of the referenced table such that every row in S matches all the referencing columns of R and the CATPN period value of R is a subset of the union of the PATPN period values of the rows in S. If some referencing column is null and another referencing column is non-null, then the row of the referencing table violates the constraint check. NOTE 69 — In the case that <referencing period specification> is specified, even though for a given row R in the referencing table there may be more than one corresponding row in the referenced table, there is at most one corresponding row in the referenced table at any given point in time in the CATPN period value of R. [If] <referencing period specification> is specified, then the unique constraint that defines the unique columns of the referenced table shall specify <without overlap specification>. If <referencing period specification> is specified, then: {{  a) <referenced table and columns> shall immediately contain a <referenced column list> and a <referenced period specification>.}} {{ b) <referential action> shall not specify CASCADE, SET NULL, or SET DEFAULT}}

Nikita Malyavin added a comment - 2020-02-26 08:37 Hello, serg ! Please review following commits on branch bb-10.5- MDEV-16983 -period-fk : Prelock child tables in addition to parent ones Add referenced table to prelocking list during CREATE TABLE with FK MDEV-16983 Application-time periods: foreign key PART 1/3 MDEV-16983 Application-time periods: foreign key PART 2/3 MDEV-16983 Application-time periods: foreign key PART 3/3 Period: add versioning combination

Nikita Malyavin added a comment - 2024-11-19 21:16 ralf.gebhardt I think there's not too much change in the related code for now. We may need some refreshment after MDEV-20865 will reach main branch.