title: Problem adding Foreign Key using Alter Table with existing MYSQL Database - can't add it! Help!
body: <p>I have a production database where I have renamed several column's that are foreign keys. Obviously mysql makes this a real pain to do in my experience.</p>
  
  <p>My solution was to drop all the indexes and foreign keys, rename the id columns, and then re-add the indexes and foreign keys.</p>
  
  <p>This works great on mysql 5.1 on windows for the development database.</p>
  
  <p>I went to run my migration script on my debian server, which is also using mysql 5.1, and it gives the following error:</p>
  
  <pre><code>mysql&gt; ALTER TABLE `company_to_module`
      -&gt; ADD CONSTRAINT `FK82977604FE40A062` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
  ERROR 1005 (HY000): Can't create table 'jobprep_production.#sql-44a5_76' (errno: 150)
  </code></pre>
  
  <p>There are no values in this table that would conflict with the foreign key I am trying to add. The database hasn't changed. The foreign key DID exist before... so the data is fine. Let's not mention that I took the SAME database that I have on the server and it migrates fine on Windows. But these same foreign key migrations are not taking on Debian.</p>
  
  <p>The columns are using the same type - BIGINT (20)</p>
  
  <p>The names do in fact exist in their respective tables.</p>
  
  <p>The tables are innodb. They already have foreign keys in other columns as it is. This is not a new database.</p>
  
  <p>I cannot drop tables because this is a production database.</p>
  
  <p>The tables "as is" in my database:</p>
  
  <pre><code> CREATE TABLE `company_to_module` (
    `company_id` bigint(20) NOT NULL,
    `module_id` bigint(20) NOT NULL,
    KEY `FK8297760442C8F876` (`module_id`),
    KEY `FK82977604FE40A062` (`company_id`) USING BTREE,
    CONSTRAINT `FK8297760442C8F876` FOREIGN KEY (`module_id`) REFERENCES `module` (`module_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>And</p>
  
  <pre><code>Create Table: CREATE TABLE `company` (
    `company_id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `address` varchar(255) DEFAULT NULL,
    `postal_code` varchar(255) DEFAULT NULL,
    `province_id` bigint(20) DEFAULT NULL,
    `phone_number` varchar(255) DEFAULT NULL,
    `is_enabled` bit(1) DEFAULT NULL,
    `director_id` bigint(20) DEFAULT NULL,
    `homepage_viewable` bit(1) NOT NULL DEFAULT b'1',
    `courses_created` int(10) NOT NULL DEFAULT '0',
    `header_background` varchar(25) DEFAULT '#172636',
    `display_name` varchar(25) DEFAULT '#ffffff',
    `tab_background` varchar(25) DEFAULT '#284767',
    `tab_text` varchar(25) DEFAULT '#ffffff',
    `hover_tab_background` varchar(25) DEFAULT '#284767',
    `hover_tab_text` varchar(25) DEFAULT '#f2e0bd',
    `selected_tab_background` varchar(25) DEFAULT '#f5f5f5',
    `selected_tab_text` varchar(25) DEFAULT '#172636',
    `hover_table_row_background` varchar(25) DEFAULT '#c0d2e4',
    `link` varchar(25) DEFAULT '#4e6c92',
    PRIMARY KEY (`company_id`),
    KEY `FK61AE555A71DF3E03` (`province_id`),
    KEY `FK61AE555AAC50C977` (`director_id`),
    CONSTRAINT `company_ibfk_1` FOREIGN KEY (`director_id`) REFERENCES `user_account` (`user_account_id`),
    CONSTRAINT `FK61AE555A71DF3E03` FOREIGN KEY (`province_id`) REFERENCES `province` (`province_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>Here is the innodb status:</p>
  
  <pre><code>------------------------
  LATEST FOREIGN KEY ERROR
  ------------------------
  110415  3:14:34 Error in foreign key constraint of table jobprep_production/#sql-44a5_1bc:
   FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT:
  Cannot resolve column name close to:
  ) ON DELETE RESTRICT ON UPDATE RESTRICT
  </code></pre>
  
  <p>If I try and drop the index from 'company_to_module', I get this error:</p>
  
  <pre><code>#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150) 
  </code></pre>
  
  <p>Here are my innodb variables:</p>
  
  <pre><code>+---------------------------------+------------------------+
  | Variable_name                   | Value                  |
  +---------------------------------+------------------------+
  | innodb_adaptive_hash_index      | ON                     |
  | innodb_additional_mem_pool_size | 1048576                |
  | innodb_autoextend_increment     | 8                      |
  | innodb_autoinc_lock_mode        | 1                      |
  | innodb_buffer_pool_size         | 8388608                |
  | innodb_checksums                | ON                     |
  | innodb_commit_concurrency       | 0                      |
  | innodb_concurrency_tickets      | 500                    |
  | innodb_data_file_path           | ibdata1:10M:autoextend |
  | innodb_data_home_dir            |                        |
  | innodb_doublewrite              | ON                     |
  | innodb_fast_shutdown            | 1                      |
  | innodb_file_io_threads          | 4                      |
  | innodb_file_per_table           | OFF                    |
  | innodb_flush_log_at_trx_commit  | 1                      |
  | innodb_flush_method             |                        |
  | innodb_force_recovery           | 0                      |
  | innodb_lock_wait_timeout        | 50                     |
  | innodb_locks_unsafe_for_binlog  | OFF                    |
  | innodb_log_buffer_size          | 1048576                |
  | innodb_log_file_size            | 5242880                |
  | innodb_log_files_in_group       | 2                      |
  | innodb_log_group_home_dir       | ./                     |
  | innodb_max_dirty_pages_pct      | 90                     |
  | innodb_max_purge_lag            | 0                      |
  | innodb_mirrored_log_groups      | 1                      |
  | innodb_open_files               | 300                    |
  | innodb_rollback_on_timeout      | OFF                    |
  | innodb_stats_on_metadata        | ON                     |
  | innodb_support_xa               | ON                     |
  | innodb_sync_spin_loops          | 20                     |
  | innodb_table_locks              | ON                     |
  | innodb_thread_concurrency       | 8                      |
  | innodb_thread_sleep_delay       | 10000                  |
  +---------------------------------+------------------------+
  </code></pre>
  
  <p>I also want to add that while I was playing with adding the foreign keys, mysql corrupted my database and destroyed it. I had to reload from a backup to try again.</p>
  
  <p>Help? :/</p>