Description:
Duplicate or conflicting foreign key constraint can be added using the alter table command.
How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to // and use the test database.
3. Create a table as follows:
create table t1(
f1 int, f2 char(100),
primary key(f1)
)engine=innodb//
4. Create another table with foreign key to column f1 of table t1 with the ON DELETE SET DEFAULT option:
create table t2(f1 int default 0, f2 char(100),
foreign key (f1) references t1(f1) ON DELETE SET NULL
)engine=innodb//
5. Now add the same constraint can be added through alter table:
alter table t2 add foreign key (f1) references t1(f1) ON DELETE SET NULL//
6. Also try to add same constraint with different option using the alter table command as follows:
alter table t2 add foreign key (f1) references t1(f1) ON DELETE RESTRICT//
Expected Results:
1. Error should be displayed for duplicate foreign key constraint.
Actual Results:
1. No error is displayed and conflicting foreign key constraints are accepted.
2. The output of show creates table statement shown below displays the duplicate constraints.
mysql> show create table t2//
+-------+---------------------------------------------------------------------------------------
| Table | Create Table
+-------+---------------------------------------------------------------------------------------
| t2 | CREATE TABLE `t2` (
`f1` int(11) default '0',
`f2` char(100) default NULL,
KEY `f1` (`f1`),
CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`),
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE SET NULL,
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql>
Description: Duplicate or conflicting foreign key constraint can be added using the alter table command. How to repeat: 1. Start the MySQL client and connect to the database with valid user and password. 2. Set the delimiter to // and use the test database. 3. Create a table as follows: create table t1( f1 int, f2 char(100), primary key(f1) )engine=innodb// 4. Create another table with foreign key to column f1 of table t1 with the ON DELETE SET DEFAULT option: create table t2(f1 int default 0, f2 char(100), foreign key (f1) references t1(f1) ON DELETE SET NULL )engine=innodb// 5. Now add the same constraint can be added through alter table: alter table t2 add foreign key (f1) references t1(f1) ON DELETE SET NULL// 6. Also try to add same constraint with different option using the alter table command as follows: alter table t2 add foreign key (f1) references t1(f1) ON DELETE RESTRICT// Expected Results: 1. Error should be displayed for duplicate foreign key constraint. Actual Results: 1. No error is displayed and conflicting foreign key constraints are accepted. 2. The output of show creates table statement shown below displays the duplicate constraints. mysql> show create table t2// +-------+--------------------------------------------------------------------------------------- | Table | Create Table +-------+--------------------------------------------------------------------------------------- | t2 | CREATE TABLE `t2` ( `f1` int(11) default '0', `f2` char(100) default NULL, KEY `f1` (`f1`), CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE SET NULL, CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------- 1 row in set (0.00 sec) mysql>