Bug #10354 When specifying a foreign key constraint reference to same column is accepted.
Submitted: 4 May 2005 11:05 Modified: 5 May 2005 8:16
Reporter: Disha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.4 Beta OS:Windows (Windows 2003)
Assigned to: CPU Architecture:Any

[4 May 2005 11:05] Disha
Description:
When we add a foreign key constraint if we specify the same column as the reference, no error is displayed.

How to repeat:
Repro Steps: 
1. Start the MySQL client and connect to the database with valid user and password.
1. Set the delimiter to //
3. Run the following commands to create a table.

   drop table if exists t1//
   create table t1(f1 int, primary key (f1))//

4. Now add a foreign key constraint as follows:

   alter table t1 add foreign key (f1) references t1(f1)//

Expected Results: 
1. Appropriate error should be displayed the same field is specified as reference for the foreign key constraint.

Actual Results: 
1. No error is displayed and the constraint is accepted.
[4 May 2005 11:23] Jan Lindström
Thank you for your bug report. I was able to repeat this problem with 5.0.6bk. Note that
primary key can be also a foreign key. Therefore, that part is not a bug. But you should not be able to specify more than one foreign key constraint to same column.

mysql> create table t1(f1 int, primary key (f1));
Query OK, 0 rows affected (0.28 sec)

mysql> alter table t1 add foreign key (f1) references t1(f1);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `f1` int(11) NOT NULL default '0',
  PRIMARY KEY  (`f1`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t1 add foreign key (f1) references t1(f1);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                           |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `f1` int(11) NOT NULL default '0',
  PRIMARY KEY  (`f1`),
  CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[4 May 2005 18:11] Heikki Tuuri
Hi!

But is this an error?

A strange way to use FOREIGN KEY, but an error?

Regards,

Heikki
[5 May 2005 8:16] Jan Lindström
I would say that this is not a error. You can have a column referencing several tables e.g.

create table t0(a int not null, primary key(a));
create table t1(b int not null, primary key(b));
create table t3(c int not null, primary key(c), foreign key (c) references t0(a), 
foreign key (c) references t1(b));

mysql> show create table t3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                     |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `c` int(11) NOT NULL default '0',
  PRIMARY KEY  (`c`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t0` (`a`),
  CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

This would need fixing if you can break the database with this feature.

Regards,
    JanL
[5 May 2005 15:59] Trudy Pelzer
For the record, the SQL Standard allows the same column
to be a foreign key for multiple unique keys, allows a
foreign key to reference itself as a unique key, and allows
duplicate foreign key definitions. So Disha has not found
a bug according to the Standard.