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

Fix REFERENCES constraint in column definition

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: