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

Adding a check constraint in "create table" doesn't work if it uses a foreign key with "update cascade"

    XMLWordPrintable

Details

    Description

      This used to work correctly, but at some point it broke.

      create table a (
        id int,
        primary key (id)
      );
       
      -- This works, because update is "restrict"
      create table b1 (
        a_id int,
        foreign key (a_id) references a(id) on update restrict,
        check (a_id > 7)
      );
       
      -- This works, because we are altering the table, even though update is "cascade"
      create table b2 (
        a_id int,
        foreign key (a_id) references a(id) on update cascade
      );
      alter table b2 add check (a_id > 7);
       
      -- But "update cascade" doesn't work in the table create
      create table b3 (
        a_id int,
        foreign key (a_id) references a(id) on update cascade,
        check (a_id > 7)
      );
      -- Error in query (1901): Function or expression 'a_id' cannot be used in the CHECK clause of `CONSTRAINT_1`
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              magmatic Tim Koop
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.