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

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30606 Foreign key cannot be used in the check constraint

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-19402 Invoke triggers for foreign key cascade actions

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-30674 Implement CHECK constraints validation on cascading updates

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-16417 Store Foreign Key metadata outside of InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18114 Foreign Key Constraint actions don't affect Virtual Column

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22880 Honor constraints on UPDATE CASCADE

          • Major - Major loss of function.
          • Open

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.