MySQL Bugs: #13429: Duplicate FOREIGN KEYs are allowed

Bug #13429	Duplicate FOREIGN KEYs are allowed
Submitted:	23 Sep 2005 15:19	Modified:	6 Oct 2005 12:06
Reporter:	Jonathan Miller	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0, 5.0.12-nt	OS:	Linux (Linux, Windows)
Assigned to:	Osku Salerma	CPU Architecture:	Any

Description:
Consider the following test:
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;

CREATE TABLE test.t1 (c1 INT PRIMARY KEY, c2 INT)ENGINE=INNODB;
CREATE TABLE test.t2 (c1 INT, c2 INT)ENGINE=INNODB;

ALTER TABLE test.t2 ADD FOREIGN KEY (c1) REFERENCES test.t1(c1);
ALTER TABLE test.t2 ADD FOREIGN KEY (c1) REFERENCES test.t1(c1);

SHOW CREATE TABLE test.t2;
------------------------------------------------- out put ---------------------------------------------
SHOW CREATE TABLE test.t2;
+ Table Create Table
+ t2    CREATE TABLE `t2` (
+   `c1` int(11) default NULL,
+   `c2` int(11) default NULL,
+   KEY `c1` (`c1`),
+   CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`),
+   CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`) <--Duplicate
+ ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-------------------------------------------------------------------------------------------------------------
The above should not be allowed. But the work around is to use syntax as follows:
---------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS test.t2;
DROP TABLE IF EXISTS test.t1;

CREATE TABLE test.t1 (c1 INT PRIMARY KEY, c2 INT)ENGINE=INNODB;
CREATE TABLE test.t2 (c1 INT, c2 INT)ENGINE=INNODB;

ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES test.t1(c1);
ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES test.t1(c1);

DROP TABLE IF EXISTS test.t2;
DROP TABLE IF EXISTS test.t1;
-------------------------------------------- results -----------------------------------------------------------
mysqltest: At line 20: query 'ALTER TABLE test.t2 ADD CONSTRAINT `ri1` FOREIGN KEY (c1) REFERENCES test.t1(c1)' failed: 1005: Can't create table './test/#sql-2eb8_1.frm' (errno: 121)
-----------------------------------------------------------------------------------------------------------------------------
But this error message does not make sense for this. Should say that FOREIGN KEY already exists. I will open a different bug for this error message.

How to repeat:
See above

Suggested fix:
Do not allow duplicate FK on tables.

This seems to me to be more than a performance problem:

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (c1 INT,
 FOREIGN KEY (c1) REFERENCES t1(c1) ON UPDATE CASCADE,
 FOREIGN KEY (c1) REFERENCES t1(c1) ON UPDATE SET NULL
) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

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

mysql> update t1 set c1 = 42;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`) ON UPDATE SET NULL)
mysql> 

So we accept contradictory constraints and then go completely astray when trying to enforce them.

Even more interesting case:

CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
CREATE TABLE t2 (c1 INT,
FOREIGN KEY (c1) REFERENCES t1(c1) ON UPDATE SET NULL,
FOREIGN KEY (c1) REFERENCES t1(c1) ON DELETE CASCADE
) ENGINE=INNODB;

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> delete from t1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `t1` (`c1`) ON UPDATE SET NULL)
mysql> 

Why is ON UPDATE obstructing a delete?

The above case works if both are listed in the same clause:
CREATE TABLE t2 (c1 INT,
FOREIGN KEY (c1) REFERENCES t1(c1) ON UPDATE SET NULL ON DELETE CASCADE
) ENGINE=INNODB;

I don't know if the SQL spec allows specifying ON UPDATE / ON DELETE behavior separately using multiple FOREIGN KEY clauses. I'm guessing no?

I'll prepare a patch which disallows duplicate foreign keys where duplicateness means having the same (table, cols, ref_table, ref_cols) information.

Osku,

a good catch. Makes sense to forbid duplicate foreign key constraints.

You can study standards from docs here:

http://www.wiscorp.com/SQLStandards.html

Note that one can also define more complex contradictory ON UPDATE clauses on different keys and columns. In those cases, we could say that the behavior is 'not deterministic'.

Regards,

Heikki

I have a working patch that disallows duplicate foreign key definitions, but it fails since the testcases actually contain cases that create those. (Here, duplicate is defined as above, i.e. not counting any action type of the constraint.)

I had a look at the SQL spec, and can't find anything there about disallowing them.

I tested with Postgres 7.4.7, and it also allows them:

osku=> \d t2
      Table "public.t2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 c1     | integer | 
Foreign-key constraints:
    "$1" FOREIGN KEY (c1) REFERENCES t1(c1) ON DELETE SET NULL
    "$2" FOREIGN KEY (c1) REFERENCES t1(c1) ON DELETE CASCADE
    "$3" FOREIGN KEY (c1) REFERENCES t1(c1)
    "$4" FOREIGN KEY (c1) REFERENCES t1(c1)

My current opinion is that this should be closed as "not a bug", since correctly handling cases where the user has expressed their ON UPDATE / ON DELETE actions in separate constraints gets quite complicated, and doing that would be necessary AFAIK.

Opinions?

I can understand that if other allow it, then we should not spend a lot of time on it. Having said that, I would be more interested in what Oracle and MS do rather then postgress.

Thanks
JBM

Jan Lindström tested on Oracle (7.something and 10.something), and it also allows them, even contradictory ones.

Therefore, I'm closing this as a "not a bug".

InnoDB permits dupe FKs on non-candidate keys:

drop table if exists c,d;
create table c( i int primary key, j int,key(j) );
insert into c values(1,1),(2,2),(3,2);
create table d( i int, j int, foreign key (j) references c(j) ) ;
insert into d values(1,2),(2,2),(3,2);

SQL Server 2005 does not. Didn't try Oracle.

I suggest it's a bug.