MySQL Bugs: #4919: Bad Foreign Key Definition does not work nor does it produce an error

Bug #4919	Bad Foreign Key Definition does not work nor does it produce an error
Submitted:	6 Aug 2004 1:45	Modified:	12 Jul 8:48
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.0.20 / 4.1.3	OS:	Any (all)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
create table department
(
department_id int not null auto_increment primary key,
name varchar(20) 
) type=InnoDB;

create table employee
(
employee_id int not null auto_increment primary key,
name varchar(80),
job varchar(15),
department_id int not null references department(department_id)
) type=InnoDB;

show create table employee\G

-- You can see that the syntax is not correct and it does not create the constraint.  It also does
not error out as you can see.

How to repeat:
create table department
(
department_id int not null auto_increment primary key,
name varchar(20) 
) type=InnoDB;

create table employee
(
employee_id int not null auto_increment primary key,
name varchar(80),
job varchar(15),
department_id int not null references department(department_id)
) type=InnoDB;

show create table employee\G

-- You can see that the syntax is not correct and it does not create the constraint.  It also does
not error out as you can see.

Suggested fix:
An error should be returned and shown directly or in show innodb status;

Hi!

This problem is well-known since 3 years. It is in the TODO to add also that syntax to FOREIGN KEYs.

MySQL does not give a syntax error, because it was the intention in the 1990's that table definitions containing FOREIGN KEY constraints should be importable to MySQL, even though MySQL at that time did not support foreign keys.

Best regards,

Heikki

I'd like to add that in my opinion, that there's nothing wrong about this syntax. I think even the standard allows this 'inline' style of foreign key definitions. 
I know for sure maxdb, oracle and ms sql support it. 

I do think that it should either report a syntax error, or create the foreign key. As such, I would definitely call this a bug - not a feature request -, because the machine does not perform the function it is supposed to do although it is perfectly capaple: you can create the foreign key through an ALTER TABLE ADD CONSTRAINT statement.

Another reason why this should not remain untouched is that the manual has some examples that rely on this syntax:

http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html

(shirt table)

I'll re-iterate what I said in http://bugs.mysql.com/bug.php?id=17943 :

This is a *bug*, not a feature request.  If you update the documentation to reflect the current behavior, *then* it will be a feature request, but until then this is an outstanding BUG.  The best thing you could do is just take care of the (probably minimal) work required to make inline FK definitions work just like out of line ones for InnoDB.  But if you're going to put this off as a feature request for sometime in the future, at least update your docs to reflect that this doesn't work yet (and/or have it spew a warning, so that the hapless users relying on this missing functionality have some idea what's going on).

Fixed in MySQL 9.0.0 by WL#16130 and WL#16131.

See same for changelog entry, other docs info.

Closed.