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

            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.