MySQL Bugs: #10469: Cannot create foreign key relation with ON DELETE SET DEFAULT option.

Bug #10469	Cannot create foreign key relation with ON DELETE SET DEFAULT option.
Submitted:	9 May 2005 11:20	Modified:	19 May 2005 1:53
Reporter:	Disha	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.5 Beta	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
Cannot create foreign key relation with ON DELETE SET DEFAULT option.

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 DEFAULT
   )engine=innodb//
   
Expected Results: 
1. A foreign key constraint should get added.

Actual Results: 
1. Error is displayed as:
ERROR 1005 (HY000): Can't create table '.\test\t2.frm' (errno: 150)
 
Additional Comments:
In the above example if we replace the ON DELETE SET DEFAULE option to ON DELETE SET NULL then the constraint is created properly.

Thank you for your bug report. However this is not a bug. InnoDB  rejects table definitions containing ON DELETE SET DEFAULT  clauses. This is documented on the manual section 15.7.4. FOREIGN KEY Constraints (see http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html).

Regards,
    JanL

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).