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

Fix REFERENCES constraint in column definition

Details

    Description

      Add support of referential constraints directly in column defininions:

      create table t1 (id1 int primary key);
      		 
      create table t2 (id2 int references t1(id1));

      Referenced field name can be omitted if equal to foreign field name:

      create table t1 (id int primary key);
      		 
      create table t2 (id int references t1);

      Until 10.5 this syntax was understood by the parser but was silently
      ignored.

      In case of generated columns this syntax is disabled at parser level
      by ER_PARSE_ERROR. Note that separate FOREIGN KEY clause for generated
      columns is disabled at storage engine level.

      From ISO/IEC 9075-2 (2016):

      11.4 <column definition> (p. 842)

      <column definition> ::=
      		 
        <column name> [ <data type or domain name> ]
      		 
                [ <default clause> | <identity column specification> | <generation clause>
      		 
                | <system time period start column specification>
      		 
                | <system time period end column specification> ]
      		 
                [ <column constraint definition>... ]
      		 
      ...
      		 
      <column constraint definition> ::=
      		 
        [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]
      		 
       
      		 
      <column constraint> ::=
      		 
                  NOT NULL
      		 
                | <unique specification>
      		 
                | <references specification>
      		 
                | <check constraint definition>

      11.8 <referential constraint definition> (p. 857)

      <references specification> ::=
      		 
        REFERENCES <referenced table and columns>
      		 
                [ MATCH <match type> ] [ <referential triggered action> ]
      		 
       
      		 
      <referenced table and columns> ::=
      		 
        <table name> [ <left paren> <referenced column list>
      		 
               [ <comma> <referenced period specification> ] <right paren> ]

      Note

      Currently this syntax is accepted but silently ignored.

      Reproduce

      create or replace table t1 (x int primary key);
      		 
      create or replace table t2 (x int references t1(x));
      		 
      show create table t2;

      Result

      +-------+---------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                          |
      		 
      +-------+---------------------------------------------------------------------------------------+
      		 
      | t2    | CREATE TABLE `t2` (
      		 
        `x` int(11) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+---------------------------------------------------------------------------------------+

      Expected

      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                                                             |
      		 
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t2    | CREATE TABLE `t2` (
      		 
        `x` int(11) DEFAULT NULL,
      		 
        KEY `x` (`x`),
      		 
        CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Attachments

        Issue Links

          duplicates

          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
          is blocked by

          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
          relates to

          Task - A task that needs to be done. MDEV-7284 INDEX: CREATE OR REPLACE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7801 Unexpected syntax error in ALTER TABLE t1 ADD INDEX TYPE BTREE

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35916 REFERENCES clause fails to infer multiple column names

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            mdw Matthias Wallnöfer added a comment -

            I guess that the documentation hasn't been updated yet in order to reflect the change: https://mariadb.com/kb/en/create-table/#column-definitions

            Note: MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE column definitions, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's.

            mdw Matthias Wallnöfer added a comment - I guess that the documentation hasn't been updated yet in order to reflect the change: https://mariadb.com/kb/en/create-table/#column-definitions Note: MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE column definitions, but that syntax does nothing . MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's.
            mdw Matthias Wallnöfer added a comment -

            Just as a follow-up: I have noticed that the bug has been filed against MySQL 8.0 too: https://bugs.mysql.com/bug.php?id=102904.

            mdw Matthias Wallnöfer added a comment - Just as a follow-up: I have noticed that the bug has been filed against MySQL 8.0 too: https://bugs.mysql.com/bug.php?id=102904 .

            People

              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              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.