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

REFERENCES clause fails to infer multiple column names

Details

    Description

      MDEV-20729 a.k.a. MDEV-6420 was not fixed completely. It seems that single-column FOREIGN KEY constraints can be created without specifying the column names twice, but multiple column names have to be specified explicitly:

      --source include/have_innodb.inc
      		 
      CREATE TABLE t1 (a INT PRIMARY KEY, b INT UNIQUE) engine=InnoDB;
      		 
      CREATE TABLE t2 (a INT PRIMARY KEY, b INT UNIQUE REFERENCES t1) ENGINE=InnoDB;
      		 
      --error ER_CANT_CREATE_TABLE,ER_FK_NO_INDEX_PARENT
      		 
      #SET STATEMENT foreign_key_checks=0
      		 
      ALTER TABLE t2 ADD FOREIGN KEY (a,b) REFERENCES t1;
      		 
      ALTER TABLE t2 ADD FOREIGN KEY (a,b) REFERENCES t1(a,b);

      I would expect the multi-column constraint to be allowed without having to use redundant syntax.

      This came up during a discussion around MDEV-35854, which is cleaning up some code after MDEV-20480.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-6420 REFERENCES syntax in CREATE TABLE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-20480 Obsolete internal parser for FK in InnoDB

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

          Task - A task that needs to be done. MDEV-20729 Fix REFERENCES constraint in column definition

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -

            People

              sanja Oleksandr Byelkin
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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