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 finally add foreign keys on 'from' and 'to' fields referencing 'users'.'id' field:

ALTER TABLE `messages` 
  ADD CONSTRAINT `messages_users_fk`
  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)

The error I get is:

ERROR: Error 1822: Failed to add the foreign key constraint. Missing index for constraint 'messages_users_fk' in the referenced table 'users'.

But 'users' table has a PRIMARY index on 'id'...

Also tried to make a smaller step and add foreign key just for 'from' field:

ALTER TABLE `messages` 
  ADD CONSTRAINT `messages_users_fk`
  FOREIGN KEY (`from` )
  REFERENCES `users` (`id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC) ;

The error is slightly different:

ERROR: Error 1825: Failed to add the foreign key constraint on table 'messages'. Incorrect options in FOREIGN KEY constraint 'cook4food/messages_users_fk'.

The types of the fields are the same (bigint(20) NOT NULL) as it was suggested as the cause of the problem in other StackOverflow threads. My tables are not partitioned (MySQL manual states this as a limitation for having foreign key constraints in InnoDB). The 'messages' table stores no rows currently, so data stored can't be the issue in any manner. I'm stuck, please help.

Answer 1

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.