Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
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
-
-
- Closed
-
- is blocked by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-7801 Unexpected syntax error in ALTER TABLE t1 ADD INDEX TYPE BTREE
-
- Open
-
-
-
- Open
-