title: MySQL - cannot add foreign key
body: <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 tried adding 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 <code>'from'</code> and <code>'to'</code> fields referencing <code>'users'.'id'</code> 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:</p>
  
  <blockquote>
    <p>ERROR: Error 1822: Failed to add the foreign key constraint. Missing index for constraint 'messages_users_fk' in the referenced table 'users'. </p>
  </blockquote>
  
  <p>But 'users' table has a <code>PRIMARY</code> index on <code>'id'</code>...</p>
  
  <p>Also tried to make a smaller step and add foreign key just for <code>'from'</code> 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:</p>
  
  <blockquote>
    <p>ERROR: Error 1825: Failed to add the foreign key constraint on table 'messages'. Incorrect options in FOREIGN KEY constraint 'cook4food/messages_users_fk'.</p>
  </blockquote>
  
  <p>The types of the fields are the same (<code>bigint(20) NOT NULL</code>) 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 <code>'messages'</code> table stores no rows currently, so data stored can't be the issue in any manner. I'm stuck, please help.</p>