title: Why does MySQL fail with ERROR 156 "Table already exists" when trying to create an index?
body: <p>I'm attempting to add uniqueness constraint on an existing table by creating an index.</p>
  
  <p>Here's the existing table (I've tried to make mysql's output a bit more readable):</p>
  
  <pre><code>mysql&gt; show create table profile_status;
  
  | Table          | Create Table
  | profile_status | CREATE TABLE `profile_status` (
                         `user_id` int(11) NOT NULL,
                         `timestamp` datetime DEFAULT NULL,
                         `proxy_profile_id` int(11) DEFAULT NULL,
                          KEY `user_id_refs_user_id` (`user_id`),
                          KEY `profile_status` (`proxy_profile_id`),
                          CONSTRAINT `proxy_profile_id_refs_user_id` FOREIGN KEY (`proxy_profile_id`) REFERENCES `profile_userprofile` (`user_id`),
                          CONSTRAINT `user_id_refs_user_id` FOREIGN KEY (`user_id`) REFERENCES `profile_userprofile` (`user_id`)
                      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Here's what happens when I attempt to create the index:</p>
  
  <pre><code>mysql&gt; CREATE UNIQUE INDEX `profile_status_unique` ON `profile_status` (`user_id`);
  ERROR 156 (HY000): Table 'project.profile_status#1' already exists
  </code></pre>
  
  <p>My first suspicion is that the error message is misleading, and in fact MySQL is in fact refusing to create a second index on the user_id column, since there's already a non-unique key there.  So I tried dropping that key first (along with the associated foreign key constraint):</p>
  
  <pre><code>mysql&gt; ALTER TABLE profile_status DROP FOREIGN KEY user_id_refs_user_id;
  Query OK, 112 rows affected (0.05 sec)
  Records: 112  Duplicates: 0  Warnings: 0
  
  mysql&gt; ALTER TABLE profile_status DROP KEY user_id_refs_user_id;
  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql&gt; show create table profile_status;
  
  | Table          | Create Table
  | profile_status | CREATE TABLE `profile_status` (
                         `user_id` int(11) NOT NULL,
                         `timestamp` datetime DEFAULT NULL,
                         `proxy_profile_id` int(11) DEFAULT NULL,
                          KEY `profile_status` (`proxy_profile_id`),
                          CONSTRAINT `proxy_profile_id_refs_user_id` FOREIGN KEY (`proxy_profile_id`) REFERENCES `profile_userprofile` (`user_id`),
                      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  
  1 row in set (0.00 sec)
  
  mysql&gt; CREATE UNIQUE INDEX `profile_status_unique` ON `profile_status` (`user_id`);
  ERROR 156 (HY000): Table 'project.profile_status#1' already exists
  </code></pre>
  
  <p>Same result.  At this point I'm thoroughly stumped, and I'd really appreciate some help.  Worst case, I could create a temporary table with the correct constraints and copy over the data, but I'd like to understand this problem fully.</p>
  
  <p>Thanks in advance.  Here's the MySQL details:</p>
  
  <pre><code>mysql&gt; show variables like '%version%';
  | Variable_name           | Value
  | innodb_version          | 1.1.8
  | protocol_version        | 10
  | version                 | 5.5.28-1
  | version_comment         | (Debian)
  | version_compile_machine | i686
  | version_compile_os      | debian-linux-gnu
  7 rows in set (0.00 sec)
  </code></pre>