MySQL - cannot add foreign key <p>I have a table in MySQL InnoDB created like that:</p> <pre><code>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 </code></pre> <p>Next I try added a table with such a statement (no foreign keys added while creating the table as had problem with that):</p> <pre><code>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 </code></pre> <p>Having tables created, I need to finally add foreign keys on 'from' and 'to' fields referencing 'users'.'id' field:</p> <pre><code>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) </code></pre> <p>The error I get is ERROR: Error 1822: Failed to add the foreign key constaint. Missing index for constraint 'messages_users_fk' in the referenced table 'users'. But 'users' table has a PRIMARY index on 'id'...</p> <p>Also tried to make a smaller step and add foreign key just for 'from field:</p> <pre><code>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) ; </code></pre> <p>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'.</p> <p>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.</p>