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: | |
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
[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.