[MDEV-20729] Fix REFERENCES constraint in column definition - Jira

Aleksey Midenkov created issue - 2019-10-02 19:47

Aleksey Midenkov made changes - 2019-10-02 19:48

Field	Original Value	New Value
Link		This issue is blocked by ~~MDEV-20480~~ [ ~~MDEV-20480~~ ]

Aleksey Midenkov made changes - 2019-10-02 19:50

Status

Open [ 1 ]

In Progress [ 3 ]

Aleksey Midenkov made changes - 2019-10-04 12:25

Link

This issue relates to MDEV-7801 [ MDEV-7801 ]

Aleksey Midenkov made changes - 2019-10-04 12:25

Link

This issue relates to ~~MDEV-7284~~ [ ~~MDEV-7284~~ ]

Aleksey Midenkov made changes - 2019-10-07 08:21

Assignee	Aleksey Midenkov [ midenok ]	Alexander Barkov [ bar ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alexander Barkov made changes - 2019-10-07 10:10

Description

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

As of version 10.5, MariaDB supports referencial constraints as separate table elements:
{code:cql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author` FOREIGN KEY (author_id) REFERENCES t1 (id)
) ENGINE = InnoDB;
{code}

Under terms of this task we'll add support of referential constraints directly in column defininions:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL REFERENCES t1 (id),
) ENGINE = InnoDB;
{code}

{quote}
Note, as of 10.5 this syntax is understood by the parser, but ignored. After this task, this syntax will actually work.
{quote}

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

Alexander Barkov made changes - 2019-10-07 10:11

Description

As of version 10.5, MariaDB supports referencial constraints as separate table elements:
{code:cql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author` FOREIGN KEY (author_id) REFERENCES t1 (id)
) ENGINE = InnoDB;
{code}

Under terms of this task we'll add support of referential constraints directly in column defininions:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL REFERENCES t1 (id),
) ENGINE = InnoDB;
{code}

{quote}
Note, as of 10.5 this syntax is understood by the parser, but ignored. After this task, this syntax will actually work.
{quote}

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

As of version 10.5, MariaDB supports referential constraints as separate table elements:
{code:cql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author` FOREIGN KEY (author_id) REFERENCES t1 (id)
) ENGINE = InnoDB;
{code}

Under terms of this task we'll add support of referential constraints directly in column defininions:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL REFERENCES t1 (id),
) ENGINE = InnoDB;
{code}

{quote}
Note, as of 10.5 this syntax is understood by the parser, but ignored. After this task, this syntax will actually work.
{quote}

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

Alexander Barkov made changes - 2019-10-07 10:13

Description

As of version 10.5, MariaDB supports referential constraints as separate table elements:
{code:cql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author` FOREIGN KEY (author_id) REFERENCES t1 (id)
) ENGINE = InnoDB;
{code}

Under terms of this task we'll add support of referential constraints directly in column defininions:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL REFERENCES t1 (id),
) ENGINE = InnoDB;
{code}

{quote}
Note, as of 10.5 this syntax is understood by the parser, but ignored. After this task, this syntax will actually work.
{quote}

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

As of version 10.5, MariaDB supports referential constraints as separate table elements:
{code:cql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author` FOREIGN KEY (author_id) REFERENCES t1 (id)
) ENGINE = InnoDB;
{code}

Under terms of this task we'll add support of referential constraints directly in column defininions:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL REFERENCES t1 (id),
) ENGINE = InnoDB;
{code}

{quote}
Note, as of 10.5 this syntax is understood by the parser, but is silently ignored. After this task, this syntax will actually work.
{quote}

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

Alexander Barkov made changes - 2019-10-07 10:14

Assignee	Alexander Barkov [ bar ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2019-11-20 04:15

Description

As of version 10.5, MariaDB supports referential constraints as separate table elements:
{code:cql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author` FOREIGN KEY (author_id) REFERENCES t1 (id)
) ENGINE = InnoDB;
{code}

Under terms of this task we'll add support of referential constraints directly in column defininions:

{code:sql}
CREATE OR REPLACE TABLE t1 (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE OR REPLACE TABLE t2 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL REFERENCES t1 (id),
) ENGINE = InnoDB;
{code}

{quote}
Note, as of 10.5 this syntax is understood by the parser, but is silently ignored. After this task, this syntax will actually work.
{quote}

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

Add support of referential constraints directly in column defininions:

{code:sql}
create table t1 (id1 int primary key);
create table t2 (id2 int references t1(id1));
{code}

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

{code:sql}
create table t1 (id int primary key);
create table t2 (id int references t1);
{code}

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.

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

Aleksey Midenkov made changes - 2019-11-20 04:19

Description

Add support of referential constraints directly in column defininions:

{code:sql}
create table t1 (id1 int primary key);
create table t2 (id2 int references t1(id1));
{code}

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

{code:sql}
create table t1 (id int primary key);
create table t2 (id int references t1);
{code}

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.

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<references specification> ::=
  REFERENCES <referenced table and columns>
          [ MATCH <match type> ] [ <referential triggered action> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

Add support of referential constraints directly in column defininions:

{code:sql}
create table t1 (id1 int primary key);
create table t2 (id2 int references t1(id1));
{code}

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

{code:sql}
create table t1 (id int primary key);
create table t2 (id int references t1);
{code}

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.

h3. From ISO/IEC 9075-2 (2016):

h4. 11.4 <column definition> (p. 842)
{code}
<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>
{code}

h4. 11.8 <referential constraint definition> (p. 857)
{code}
<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> ]
{code}

h4. Note
Currently this syntax is accepted but silently ignored.
h3.
h4. Reproduce
{code:sql}
create or replace table t1 (x int primary key);
create or replace table t2 (x int references t1(x));
show create table t2;
{code}
h4. Result
{code}
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
{code}
h4. Expected
{code}
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}

Aleksey Midenkov made changes - 2019-11-20 16:54

Fix Version/s		10.5.0 [ 23709 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Matthias Wallnöfer added a comment - 2021-05-01 07:41

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.

Matthias Wallnöfer added a comment - 2021-05-01 07:41 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.

Matthias Wallnöfer made changes - 2021-06-02 17:30

Link

This issue duplicates ~~MDEV-6420~~ [ ~~MDEV-6420~~ ]

Matthias Wallnöfer added a comment - 2021-06-02 17:32

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.

Matthias Wallnöfer added a comment - 2021-06-02 17:32 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 .

Sergei Golubchik made changes - 2021-12-06 21:24

Workflow

MariaDB v3 [ 100142 ]

MariaDB v4 [ 134106 ]

Marko Mäkelä made changes - 2025-01-23 14:31

Link

This issue relates to MDEV-35916 [ MDEV-35916 ]

MariaDB Server

Fix REFERENCES constraint in column definition

Details

Description

From ISO/IEC 9075-2 (2016):

11.4 <column definition> (p. 842)

11.8 <referential constraint definition> (p. 857)

Note

Reproduce

Result

Expected

Attachments

Issue Links

Activity

People

Dates

Git Integration