Bug #1080 FOREIGN KEY (col) REFERENCES refTable(refCol) ON DELETE SET DEFAULT gives error

• Submitted: 18 Aug 2003 12:39
• Modified: 20 Aug 2003 5:13
• Reporter: Allen Arakaki
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.0.14, 4.0.13
• OS: NT, Linux
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[18 Aug 2003 12:39] Allen Arakaki

Description:
Regardless of OS, when creating an innodb table, using ON DELETE SET DEFAULT foreign key constraint results in the following error:

ERROR 1005: Can't create table XXX (errno: 150)

This occurs when 3 tables have "nested contraints" - e.g. table1 <-contraint on table2 <-contraint on table3

I've tried the following:

FOREIGN KEY (col) REFERENCES refTable(refCol) ON DELETE SET DEFAULT - error
FOREIGN KEY (col) REFERENCES refTable(refCol) ON DELETE SET NULL - works fine!
FOREIGN KEY (col) REFERENCES refTable(refCol) ON DELETE RESTRICT  - works fine!
FOREIGN KEY (col) REFERENCES refTable(refCol) ON DELETE CASCADE - works fine!

How to repeat:
The following will result in an error when trying to create table3.  The contraint works when there are only 2 tables.

DROP TABLE table1;
CREATE TABLE table1(
itemId                  BIGINT NOT NULL PRIMARY KEY,
)TYPE = InnoDB;

DROP TABLE table2;
CREATE TABLE table2(
itemId                  BIGINT NOT NULL UNIQUE KEY,
FOREIGN KEY (itemId) REFERENCES table1(itemId) ON DELETE CASCADE
)TYPE = InnoDB;

DROP TABLE table3;
CREATE TABLE table3(
id                      BIGINT DEFAULT '0',
INDEX                   idx_id(id),
FOREIGN KEY (id) REFERENCES table2(itemId) ON DELETE SET DEFAULT
)TYPE = InnoDB;

[20 Aug 2003 5:13] Heikki Tuuri

Hi!

InnoDB does not support ON ... SET DEFAULT.

Regards,

Heikki

"
The syntax of a foreign key constraint definition in InnoDB: 

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
                  REFERENCES table_name (index_col_name, ...)
                  [ON DELETE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]
                  [ON UPDATE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]         
"