Bug #15046 constraint incorrectly enforced

• Submitted: 18 Nov 2005 10:01
• Modified: 13 May 2010 16:04
• Reporter: Aleksandar Ivanisevic
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 4.1.15
• OS: Linux (linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[18 Nov 2005 10:01] Aleksandar Ivanisevic

Description:

foreign key is not correctly enforced when referenced column is not primary key (unique index).

In the example below the update statement should be allowed because after the update there would  still be one row in table a that has i=0.

How to repeat:

mysql> create table a (i int, key(i)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> create table b (i int, foreign key(i) references a(i)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table b;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table a;
Query OK, 0 rows affected (0.02 sec)

mysql> create table a (i int, j int, key(i)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> create table b (i int, foreign key(i) references a(i)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into a values (0,0),(0,1); 
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into b values (0);
Query OK, 1 row affected (0.01 sec)

mysql> update a set i=1 where j=0;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

[18 Nov 2005 11:23] Heikki Tuuri

Hi!

SQL standards actually would require that the referenced columns carry a UNIQUE constraint. Then this situation will not arise, since only a single row in the referenced table can be the referenced one.

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
"
         2) Case:

            a) If the <referenced table and columns> specifies a <reference
              column list>, then the set of column names of that <refer-
              ence column list> shall be equal to the set of column names
              in the unique columns of a unique constraint of the refer-
              enced table. Let referenced columns be the column or columns
              identified by that <reference column list> and let refer-
              enced column be one such column. Each referenced column shall
              identify a column of the referenced table and the same column
              shall not be identified more than once.

            b) If the <referenced table and columns> does not specify a
              <reference column list>, then the table descriptor of the
              referenced table shall include a unique constraint that spec-
              ifies PRIMARY KEY. Let referenced columns be the column or
              columns identified by the unique columns in that unique con-
              straint and let referenced column be one such column. The
              <referenced table and columns> shall be considered to implic-
              itly specify a <reference column list> that is identical to
              that <unique column list>.
"

I am changing this bug report to a 'Feature request'.

Regards,

Heikki