title: MySQL - Error 1823: Failed to add the foreign key constraint to system tables
body: <p>I'm updating a database to correct the foreign key behaviour on update and delete. However, on one table in particular this has been unexpectedly difficult.</p>
  
  <p>On most tables, I've been doing the following without any issues:</p>
  
  <ol>
  <li>Drop the foreign key constraint</li>
  <li>Drop the corresponding index</li>
  <li>Add a new, updated foreign key</li>
  </ol>
  
  <p>One table, however, is causing some trouble I can't quite understand. Trying to do the process above returns the following error (I've redacted the DB name to <code>db_name</code>):</p>
  
  <pre><code>SQLSTATE[HY000]: General error: 1025 Error on rename of 
  './&lt;db_name&gt;/#sql-5e1_1c5' to './&lt;db_name&gt;/doctors' 
  (errno: 150 - Foreign key constraint is incorrectly formed)
  (SQL: alter table `doctors` drop index `doctors_id_unique`)
  </code></pre>
  
  <p>The relevant part of the table is as follows, initially</p>
  
  <pre><code>  UNIQUE KEY `doctors_id_unique` (`id`),
    UNIQUE KEY `doctors_code_unique` (`code`),
    KEY `doctors_updated_by_foreign` (`updated_by`),
    CONSTRAINT `doctors_id_foreign` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  </code></pre>
  
  <p>I want to change <code>id</code> to a primary key <em>and</em> foreign key, so I do the following:</p>
  
  <pre><code>ALTER TABLE doctors DROP FOREIGN KEY doctors_id_foreign;
  ALTER TABLE doctors DROP INDEX doctors_code_unique;
  ALTER TABLE doctors DROP INDEX doctors_id_unique;
  </code></pre>
  
  <p>On that 3rd statement, however, I get the following error:</p>
  
  <pre><code>Error on rename of './&lt;db_name&gt;/#sql-5e1_2' to './&lt;db_name&gt;/doctors' 
  (errno: 150 - Foreign key constraint is incorrectly formed)
  </code></pre>
  
  <p>I can't figure out how to fix that. I try dropping the <code>updated_by</code> constraint and key as well as the <code>code</code> unique key first, but it doesn't change anything. I try disabling foreign key checks, but no cigar.</p>
  
  <p>I try changing the SQL dump I had been using for tests. Changing it to the following made it work as I had intended, at least apparently:</p>
  
  <pre><code>  PRIMARY KEY (`id`),
    UNIQUE KEY `doctors_code_unique` (`code`),
    KEY `doctors_updated_by_foreign` (`updated_by`),
    CONSTRAINT `doctors_id_foreign` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 
  </code></pre>
  
  <p>However, I would rather update the table programatically so I can make the migration more easily and reliably. So I try a different approach and just rename the table, copy it sans keys to a new one, and add the keys there:</p>
  
  <pre><code>SET FOREIGN_KEY_CHECKS=0;
  RENAME TABLE `doctors` TO `old_doctors`;
  CREATE TABLE `doctors` SELECT * FROM `old_doctors`;
  ALTER TABLE `doctors` ADD PRIMARY KEY (`id`);
  ALTER TABLE `doctors` ADD UNIQUE INDEX `doctors_code_unique` (`code`);
  ALTER TABLE `doctors` ADD CONSTRAINT `doctors_id_foreign` FOREIGN KEY `doctors_id_foreign` (`id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
  ALTER TABLE `doctors` ADD CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY `doctors_updated_by_foreign` (`updated_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE SET NULL;
  SET FOREIGN_KEY_CHECKS=1;
  </code></pre>
  
  <p>And now I get the following error:</p>
  
  <pre><code>SQLSTATE[HY000]: General error: 1823 Failed to add the foreign key constraint
  '&lt;db_name&gt;/doctors_id_foreign' to system tables (SQL: ALTER TABLE `doctors`
  ADD CONSTRAINT `doctors_id_foreign` FOREIGN KEY `doctors_id_foreign` (`id`) 
  REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE)
  </code></pre>
  
  <p>So I just can't get it to work, and neither error seems to be common: I couldn't find any good information on how to deal with either of them.</p>
  
  <p>This has been so time-consuming that I'm probably going to put the servers down, dump the DB, fix the dump manually and import the fixed dump back; however, I'd really like to know if this is some bug or if I'm just missing something. Which is highly likely, because I'm hardly an experienced programmer.</p>
  
  <p>For context: this is all on a Laravel (5.7.13) project, but the errors happen both using Laravel migrations (with Schema and Blueprint) and directly through the SQL console. MySQL version 5.7.22.</p>
  
  <p><strong>tl;dr:</strong> I just can't change a table's foreign key's behaviour on update/delete, despite doing so successfully on various other tables on the same DB. I tried two vastly different approaches to fixing it, and both returned (distinct) errors, resolutions of which I couldn't find anywhere. Is it a bug or am I missing something?</p>