Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22602

WITHOUT OVERLAPS constraint is ignored when update is performed via foreign key cascade

    XMLWordPrintable

    Details

      Description

      create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=InnoDB;
      create or replace table t2 (a int, s date, e date, period for p(s,e), primary key(a, p without overlaps), foreign key(a,e,s) references t1(f1,f3,f2) on delete cascade on update cascade) engine=InnoDB;
      insert into t1 values (1,'2020-01-01','2021-01-01'),(2,'2020-06-01','2020-07-01');
      insert into t2 values (1,'2020-01-01','2021-01-01'),(2,'2020-06-01','2020-07-01');
       
      update t1 set f1 = 2 where f1 = 1;
      select * from t2;
      

      10.5 69077dea25

      MariaDB [test]> update t1 set f1 = 2 where f1 = 1;
      Query OK, 1 row affected (0.024 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> select * from t2;
      +---+------------+------------+
      | a | s          | e          |
      +---+------------+------------+
      | 2 | 2020-06-01 | 2020-07-01 |
      | 2 | 2020-01-01 | 2021-01-01 |
      +---+------------+------------+
      2 rows in set (0.001 sec)
      

      So, now we have a non-unique key with an overlap.

      Traditional PK would prevent the update by throwing ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: