Description:
According to the SQL spec, one can define a foreign key in one of two ways:
either as part of a column definition or as part of a table definition. Currently, it appears MySQL only supports the method when doing this as a table definition. This wouldn't be too bad, if it threw an error when attempting to use the column definition syntax, but it appears to silently swallow that form of syntax without warning or error, and create a table without that FK relationship.
How to repeat:
This works:
create table parent(id int not null, primary key (id)) engine=innodb;
create table child (id int not null, parent_id int not null, foreign key (parent_id)
insert into parent values (1);
insert into child values (10,1);
insert into child values (10,2);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
However, this fails:
create table parent (id int not null, primary key (id)) engine=innodb;
create table child (id int not null, parent_id int not null references parent(id)) engine=innodb;
insert into parent values (1); Query OK, 1 row affected (0.00 sec)
insert into child values (10,1); Query OK, 1 row affected (0.01 sec)
insert into child values (10,2); Query OK, 1 row affected (0.01 sec)
No error is reported. Here's a snippet of the proposed SQL spec (the free one from before it cost money) that says this should work:
11.3 <table definition>
Function
Define a persistent base table, a created local temporary table, or
a global temporary table.
Format
<table definition> ::=
CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE <table name>
<table element list>
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
<table element list> ::=
<left paren> <table element> [ { <comma> <table element> }... ] <right paren>
<table element> ::=
<column definition>
| <table constraint definition>
BIG SNIP and we now post the syntax for a <column definition> element:
11.4 <column definition>
Function
Define a column of a table.
Format
<column definition> ::=
<column name> { <data type> | <domain name> }
[ <default clause> ]
[ <column constraint definition>... ]
[ <collate clause> ]
<column constraint definition> ::=
[ <constraint name definition> ]
<column constraint>
[ <constraint attributes> ]
<column constraint> ::=
NOT NULL
| <unique specification>
| <references specification>
| <check constraint definition>
Now, further along in the column definition section, it says:
c) If a <column constraint definition> is specified that con-
tains a <references specification>, then it is equivalent to
a <table constraint definition> that contains the following
<table constraint>:
CND FOREIGN KEY (C) <references specification> CA
Note: The <references specification> is defined in Subclause 11.8,
"<referential constraint definition>".
So it's pretty clear that this syntax is SQL spec compliant.
Now, if MySQL threw an error when it didn't accept this syntax, this would be a low level error, only a bother that you had to change to a different syntax.
However, accepting this syntax with NO ERRORS and then not creating the defined foreign keys means that one could define tables that one thought had FK constraints that didn't. Since there's a work around, i.e. use the table constraint syntax, I guess it only qualifies as a level 2 bug.
Suggested fix:
If someone's gonna fix this bug, I suggest paying attention to the whole section of the SQL spec that covers column definition, especially the parts that say things like the above "If a <column constraint definition> is specified that contains a <references specification>, then it is equivalent to a <table constraint definition>..." for other column definition elements.
Description: According to the SQL spec, one can define a foreign key in one of two ways: either as part of a column definition or as part of a table definition. Currently, it appears MySQL only supports the method when doing this as a table definition. This wouldn't be too bad, if it threw an error when attempting to use the column definition syntax, but it appears to silently swallow that form of syntax without warning or error, and create a table without that FK relationship. How to repeat: This works: create table parent(id int not null, primary key (id)) engine=innodb; create table child (id int not null, parent_id int not null, foreign key (parent_id) insert into parent values (1); insert into child values (10,1); insert into child values (10,2); ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails However, this fails: create table parent (id int not null, primary key (id)) engine=innodb; create table child (id int not null, parent_id int not null references parent(id)) engine=innodb; insert into parent values (1); Query OK, 1 row affected (0.00 sec) insert into child values (10,1); Query OK, 1 row affected (0.01 sec) insert into child values (10,2); Query OK, 1 row affected (0.01 sec) No error is reported. Here's a snippet of the proposed SQL spec (the free one from before it cost money) that says this should work: 11.3 <table definition> Function Define a persistent base table, a created local temporary table, or a global temporary table. Format <table definition> ::= CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE <table name> <table element list> [ ON COMMIT { DELETE | PRESERVE } ROWS ] <table element list> ::= <left paren> <table element> [ { <comma> <table element> }... ] <right paren> <table element> ::= <column definition> | <table constraint definition> BIG SNIP and we now post the syntax for a <column definition> element: 11.4 <column definition> Function Define a column of a table. Format <column definition> ::= <column name> { <data type> | <domain name> } [ <default clause> ] [ <column constraint definition>... ] [ <collate clause> ] <column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint attributes> ] <column constraint> ::= NOT NULL | <unique specification> | <references specification> | <check constraint definition> Now, further along in the column definition section, it says: c) If a <column constraint definition> is specified that con- tains a <references specification>, then it is equivalent to a <table constraint definition> that contains the following <table constraint>: CND FOREIGN KEY (C) <references specification> CA Note: The <references specification> is defined in Subclause 11.8, "<referential constraint definition>". So it's pretty clear that this syntax is SQL spec compliant. Now, if MySQL threw an error when it didn't accept this syntax, this would be a low level error, only a bother that you had to change to a different syntax. However, accepting this syntax with NO ERRORS and then not creating the defined foreign keys means that one could define tables that one thought had FK constraints that didn't. Since there's a work around, i.e. use the table constraint syntax, I guess it only qualifies as a level 2 bug. Suggested fix: If someone's gonna fix this bug, I suggest paying attention to the whole section of the SQL spec that covers column definition, especially the parts that say things like the above "If a <column constraint definition> is specified that contains a <references specification>, then it is equivalent to a <table constraint definition>..." for other column definition elements.