MySQL Bugs: #11049: Foreign keys written as column constraints are ignored

Bug #11049	Foreign keys written as column constraints are ignored
Submitted:	2 Jun 2005 18:06	Modified:	3 Jun 2005 7:51
Reporter:	Thomas Padron-McCarthy	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1.12 and 5.0.2	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Foreign keys are checked when written as table constraints
(that is, withthe keywords "foreign key"),
but when written as column constraints
(that is, with "references" directly in the column definition)
they are ignored without warning.

The column-constraint syntax is allowed by SQL-99 (and, I assume, SQL:2003),
but (as far as I have found) not documented in MySQL.

How to repeat:
Give the following SQL commands:

create table dept
(id integer not null primary key,
name varchar(10)) engine=InnoDB;

/* This shows the table-constraint syntax for declaring a foreign key constraint */
create table worker1
(id integer not null primary key,
name varchar(10),
dept integer not null,
foreign key (dept) references dept(id)) engine=InnoDB;

/* This shows the column-constraint syntax for declaring a foreign key constraint */
create table worker2
(id integer not null primary key,
name varchar(10),
dept integer not null references dept(id)) engine=InnoDB;

insert into dept values (1, 'Computer');

insert into worker1 values (1, 'Bob', 1);
insert into worker1 values (2, 'Bob', 2); /* Fails, as expected */

insert into worker2 values (1, 'Bob', 1);
insert into worker2 values (2, 'Bob', 2); /* Surprise: Does NOT fail */

Suggested fix:
Either give an error for the column-constraint syntax, or handle it the same way as the table-constraint syntax.

Corrected the synopsis. It said "row constraints".

Thanks for your bug report.  I was able to reproduce this bug in 5.0.7 from bk:

mysql> insert into worker1 values (1, 'Bob', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into worker1 values (2, 'Bob', 2); /* Fails, as expected */
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql> 
mysql> insert into worker2 values (1, 'Bob', 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into worker2 values (2, 'Bob', 2); /* Surprise: Does NOT fail */
Query OK, 1 row affected (0.00 sec)

Manual section 15.7.4 says that:

"The syntax of a foreign key constraint definition in InnoDB looks like this:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]"

Similarly in section 13.2.5:

create_definition:
    column_definition
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)

column_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
        [COMMENT 'string'] [reference_definition]

reference_definition:
    REFERENCES tbl_name [(index_col_name,...)]
               [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
               [ON DELETE reference_option]
               [ON UPDATE reference_option]

Thus, at the moment column constraints are not supported. At the moment column constraints are indeed ignored (error should be issued see bug #4919). There is already feature request to support this feature (see bug #7427).