Question: I am trying to create a table named EMPLOYEE. When I use the following statements without "ON DELETE SET DEFAULT" it is working. 

Here is the Error I get with "ON DELETE SET DEFAULT":

```
ERROR 1005 (HY000): Can't create table 'COMPANY.EMPLOYEE' (errno: 150)

```

Here is the DDL

```
CREATE TABLE EMPLOYEE ( 
    Fname VARCHAR(15) NOT NULL, 
    Minit CHAR, Lname VARCHAR(15) NOT NULL, 
    Ssn CHAR(9) NOT NULL DEFAULT '123456789',
    Bdate DATE, ADDRESS VARCHAR(30), 
    Sex CHAR, Salary DECIMAL(10,2), 
    Super_Ssn CHAR(9) NOT NULL DEFAULT '123456789', 
    Dno INT NOT NULL DEFAULT -99,
    PRIMARY KEY (Ssn), 
    FOREIGN KEY (Super_Ssn) REFERENCES COMPANY.EMPLOYEE(Ssn) 
        ON DELETE SET DEFAULT 
        ON UPDATE CASCADE )ENGINE=InnoDB; 

```

Please help me!!! and Thanks in advance :)

Answer: 
You can't use `ON DELETE SET DEFAULT` or `ON UPDATE SET DEFAULT` with InnoDB

> 
> **[InnoDB and FOREIGN KEY Constraints](http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html)**  
> 
>  While SET DEFAULT is allowed by the MySQL Server, **it is rejected as
>  invalid by InnoDB**. CREATE TABLE and ALTER TABLE statements using this
>  clause are not allowed for InnoDB tables.
> 
> 
> 

You may try `ON DELETE SET NULL` if it fits your needs

> 
> If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same
>  table it has previously updated during the cascade, it acts like
>  RESTRICT. This means that you cannot use self-referential ON UPDATE
>  CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite
>  loops resulting from cascaded updates. **A self-referential ON DELETE
>  SET NULL, on the other hand, is possible, as is a self-referential ON
>  DELETE CASCADE.** Cascading operations may not be nested more than 15
>  levels deep
> 
> 
> 

Here is **[SQLFiddle](http://sqlfiddle.com/#!2/0ecee)** demo
> 
> You delete your parent table, which triggers the set default in the
>  child. The DB tries to set the child records to their default 0. But
>  there's no 0 record in the parent table, triggering the foreign key
>  violation.
> 
> 
> 

Check out the MySQL manual about [foreign key constrains](http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html):

> 
> If you re-create a table that was dropped, it must have a definition
>  that conforms to the foreign key constraints referencing it. It must
>  have the right column names and types, and it must have indexes on the
>  referenced keys, as stated earlier. If these are not satisfied, MySQL
>  returns error number 1005 and refers to error 150 in the error
>  message.
> 
> 
> 

A few ideas:

* Better drop the tables and create it new with a well formed syntax.
* Make sure to add `ENGINE=InnoDB;` to your `CREATE TABLE` - command.
* Make sure InnoDB is enabled on your MySQL server. To verify this, try this command: `SHOW VARIABLES LIKE 'have_innodb';` - if it returns a YES, then InnoDB is enabled.
* Check your command for upper- and lowercases in table- and fieldnames.
* Check this not only one the table you want to create, but also on the tables the foreign keys are referring to.
* Make sure your referred tables are properly indexed.

"When creating a foreign key constraint, MySQL requires a usable index on both the referencing table and also on the referenced table. The index on the referencing table is created automatically if one doesn't exist, but the one on the referenced table needs to be created manually (Source). Yours appears to be missing."

See [MySQL Foreign Key Error 1005 errno 150](https://stackoverflow.com/questions/4063141/mysql-foreign-key-error-1005-errno-150)