Can't create mysql table with foreign key

Question

I am getting errno 150 when I try to create the following two tables.

 CREATE TABLE `mydatabase`.`userstatus`( 
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , 
   `name` VARCHAR(50) NOT NULL , 
   `description` VARCHAR(255) , 
   PRIMARY KEY (`id`)
 );



CREATE TABLE `mydatabase`.users( 
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , 
   `email` VARCHAR(200) NOT NULL , 
   `username` VARCHAR(20) NOT NULL DEFAULT 'Unknown' , 
   `userstatusid` INT UNSIGNED NOT NULL DEFAULT 2 , 
   `datemodified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `datecreated` DATETIME DEFAULT 0 NOT NULL ,
   PRIMARY KEY (`id`),
   UNIQUE (username),
   UNIQUE (email),
   INDEX userstatusid_index (userstatusid),
   CONSTRAINT fk_users_userstatus FOREIGN KEY (userstatusid) REFERENCES userstatus(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE

 )  ENGINE=INNODB  ROW_FORMAT=DEFAULT ;

What's causing the error and how do I fix it?

Answer 1

Stupid me! I put on delete set null but the column has been defined as not null. I was focusing too much attention to the types which usually causes the errno 150.

Answer 2

Maybe you could try set a Unique key on userstatusid. Sometimes that may help.