MySQL Bugs: #11232: Return a warning if creating a FOREIGN KEY on storage engines w/out support

Bug #11232	Return a warning if creating a FOREIGN KEY on storage engines w/out support
Submitted:	10 Jun 2005 9:56	Modified:	10 Jun 2005 13:49
Reporter:	Tobias Asplund	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	*	OS:	Any (*)
Assigned to:		CPU Architecture:	Any

Description:
It's very bad that MySQL does nothing in the way of complaining if you try to define a foreign key on a table that silently ignores the definition in the creation.

How to repeat:
mysql> CREATE TABLE parent ( a INT PRIMARY KEY ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE child ( b INT, FOREIGN KEY (b) REFERENCES parent (a)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE child \G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `b` int(11) default NULL,
  KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

could probably be a good idea to give a hard error if the sql_mode no_engine_substitution is active instead of warning.

For WL#148 (Foreign keys: all storage engines) the decision was:
We will store foreign-key information, even for storage engines
like BLACKHOLE. This doesn't mean we'll always use them.