MySQL Bugs: #17943: Foreign Keys: inline foreign key constraint definition should give a warning

Bug #17943	Foreign Keys: inline foreign key constraint definition should give a warning
Submitted:	5 Mar 2006 20:19	Modified:	18 Aug 2006 8:49
Reporter:	Roland Bouman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1.7 beta	OS:	Windows (win xp)
Assigned to:		CPU Architecture:	Any

Description:
According to the manual, we can create foreign keys using inline definition, like so:

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

this is supposed to create a foreign key constraint that links the owner column to the id column of the person table. (see: http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html)

however, the foreing key is not created at all.

How to repeat:
do the ddl from http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

then, do a show create table on shirt:

mysql> show create table shirt
    -> ;
+-------+------------------------------------------------------
---------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table

                                                              |
+-------+------------------------------------------------------
---------------------------------------------------------------
--------------------------------------------------------------+
| shirt | CREATE TABLE `shirt` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `style` enum('t-shirt','polo','dress') NOT NULL,
  `color` enum('red','blue','orange','white','black') NOT NULL,
  `owner` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------
---------------------------------------------------------------
--------------------------------------------------------------+

Suggested fix:
don't ignore inline foreign key constraint definitions. Create foreign keys instead
Alternatively, actively reject the syntax and modify documentation accordingly.

Duplicate of http://bugs.mysql.com/bug.php?id=4919

I know this is now marked as duplicate - Indeed it is a duplicate of bug 4919.

However, that bug reports this as a feature request - I disagree.

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)

Roland,

I agree that not giving a warning or error on the table creation is a bug. MySQL has a tradition of silently swallowing CHECK contraint definitions etc. I would like MySQL to give a warning in that case. This has been discussed all the time since 2000 or earlier.

I have now updated the synopsis of this bug report and marked it as a Verified S3 bug.

Adding the foreign key checking to InnoDB also for this syntax is in the TODO. That is considered a feature request.

Regards,

Heikki

Heikki, 

thanks for taking it into consideration and updating the severity. I don't really mind the lack of functionality - but then, it really needs to be documented someplace where you expect it too. So, in my opinion, having an example in the docs that suggests it's a can-do is a wrong decision.

Thanks again, I appreciate it a lot.

Just want to add why it is so very confusing not to see a warning or error
- there is technically no reason why the fk constraint is not created, as it can be created using ALTER TABLE syntax
- inline definitions of UNIQUE or PRIMARY KEY constraints are honoured, resulting in creation of the constraint.

Just want to add that the syntax diagram for CREATE TABLE in the reference manual  (http://dev.mysql.com/doc/refman/5.1/en/create-table.html) includes this syntax, but does not explain it. 

At least, the documentation should warn you that the syntax is silenty ignored.

I have wasted quite some time finding out that this syntax is not supported - it would be great if this support could be added. At the very least, please clearly note in the manual that the inline syntax is not supported, currently the manual is confusing and leads developers to believe it's otherwise.

I believe the bug severity should be bumped at least until the documentation is fixed.

http://bugs.mysql.com/bug.php?id=20563 is probably a duplicate

Nah, sorry - forget my prior remark - it is not a duplicate. It just relates to 'column constraints'.

This is most definitely a bug, not a feature request, and I'm getting tired of running into people who are unwitting victims of this bug.

I understand MySQL's position wrt silently ignoring the FK constraint under MyISAM, but InnoDB does support FKs, and supports out-of-line FK defs, yet ignores the documented (and standard on other DBs) inline FK defs.

The very least you could do is update the documentation for CREATE TABLE to not show the inline FK definition syntax, and/or put a big bold warning there that such things will be silently ignored.

The best would be to make it actually work for InnoDB like it should and leave the docs alone.

Another reasonable middle ground would be to update the docs and add a warning when encountering inline FK defs on an InnoDB table.

Either way, something needs to be done.  There are people out there who think they've correctly defined foreign key constraints in their database, when they in fact have none.  They followed the documentation and received no warnings or errors...

According to the online manual (http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html), this syntax should generate an error message if used with InnoDB. However it is also silently ignored in this case (i.e. doesn't create a foreign key constraint either):

mysql> create table one (a int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> create table two (a int references one(a)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

This is more a feature request for the MySQL server.

Seriously, 8 years old now?! Emitting a warning should not be too much to ask here. Especially after it's explicitly called out in the documentation as being unsupported. It should really even be treated as an error in my opinion.