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

[10 Jun 2005 9:56] Tobias Asplund
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)
[18 Jul 2006 20:12] Tobias Asplund
could probably be a good idea to give a hard error if the sql_mode no_engine_substitution is active instead of warning.
[15 Feb 2009 18:55] Peter Gulutzan
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.