MySQL - cannot add foreign key

Question

I have a table in MySQL InnoDB created like that: ``` CREATE TABLE `users` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `type` enum('MANUAL','FACEBOOK') NOT NULL DEFAULT 'MANUAL', `role` enum('COOK','HOST','ALL') NOT NULL DEFAULT 'ALL', `about_me` varchar(1000) DEFAULT NULL, `food_preferences` varchar(1000) DEFAULT NULL, `cooking_experience` varchar(1000) DEFAULT NULL, `with_friend` bit(1) DEFAULT b'0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ``` Next I tried adding a table with such a statement (no foreign keys added while creating the table as had problem with that): ``` CREATE TABLE `messages` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `from` bigint(20) NOT NULL, `to` bigint(20) NOT NULL, `content` varchar(10000) NOT NULL, `timestamp_sent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `timestamp_read` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ``` Having tables created, I need to fi…

Accepted Answer

If you have the appropriate privileges, you can issue this query:

```
SHOW ENGINE innodb STATUS
```

... which will tell you (among some other info) the exact details of the error:

```
------------------------
LATEST FOREIGN KEY ERROR
------------------------
130311 13:30:06 Error in foreign key constraint of table test/#sql-71c_6:

FOREIGN KEY (`from` , `to` )
  REFERENCES `users` (`id` , `id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC, `to` ASC):
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.
```

On parent record deletion, you can't make child record NULL because the columns are `NOT NULL`:

```
  `from` bigint(20) NOT NULL,
  `to` bigint(20) NOT NULL,
```

Edit: Additionally, I can't see the purpose of a single composite key. I think you want two single keys instead.

MySQL - cannot add foreign key

Solution

Problem