Bug #11242 Error 1005 while creating table
Submitted: 10 Jun 2005 12:35 Modified: 10 Jun 2005 14:20
Reporter: [ name withheld ] (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.6 OS:Windows (WindowsXP)
Assigned to: CPU Architecture:Any

[10 Jun 2005 12:35] [ name withheld ] 
Description:
While creating table foo I get this error:

#1005 - Can't create table './test/foo.frm' (errno: 150)

How to repeat:
Run those statements:

CREATE TABLE lookup ( 
    id INT NOT NULL, 
      value varchar(200) NOT NULL default '', 
      PRIMARY KEY  (id), 
      UNIQUE KEY value (value) 
) TYPE=InnoDB; 

CREATE TABLE foo ( 
    lookup_id INT NOT NULL, 
    shipping_first_name varchar(50) NOT NULL default '', 
      shipping_last_name varchar(50) NOT NULL default '', 
      shipping_address1 varchar(100) NOT NULL default '', 
    INDEX lookup_id (lookup_id), 
    FOREIGN KEY (lookup_id) REFERENCES lookup(id) 
    ON UPDATE CASCADE ON DELETE SET NULL 
) TYPE=InnoDB;
[10 Jun 2005 14:20] MySQL Verification Team 
Hello,

Thank you for the report, but this is not a bug.
You defined column as NOT NULL but try to  create foreign key constraint with ON DELETE SET NULL.

Use SHOW INNODB STATUS to see latest foreign key error.