MySQL Bugs: #102904: Implement "inline REFERENCES specifications" on CREATE TABLE

Bug #102904	Implement "inline REFERENCES specifications" on CREATE TABLE
Submitted:	10 Mar 2021 14:07	Modified:	12 Jul 2024 8:55
Reporter:	Lukas Eder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I was testing various syntax and was *very* suprised to find that MySQL currently parses but ignores "inline REFERENCES specifications":

create table t (i int, primary key (i));

-- This parses just fine and is ignored
create table u (i varchar(10) references t (i));

-- This fails as expected
create table u (i varchar(10), foreign key (i) references t (i));

The error on the latter is:
SQL Error [3780] [HY000]: Referencing column 'i' and referenced column 'i' in foreign key constraint 'u_ibfk_1' are incompatible.

Given that the inline specification is just syntax sugar for the explicit specification, I don't see why this (documented!) restriction is maintained

Documentation here:
https://dev.mysql.com/doc/refman/8.0/en/create-table.html

> MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

How to repeat:
This should fail because of incompatible types:

create table t (i int, primary key (i));
create table u (i varchar(10) references t (i));

Suggested fix:
Implement the feature, which is a low hanging fruit and avoids subtle bugs because the syntax is parsed just fine (instead of failing early!)

Hi Mr. Eder,

Thank you for your bug report.

We consider it as a valuable new feature request.

Verified as a feature request.

This is implemented by WL#16130 and WL#16131 in MySQL 9.0.0.

See same for docs and other info.

Closed.

Thank you, Jon.