Jump to content
Main menu
Main menu
move to sidebar
hide
Navigation
Main page
Current events
Recent changes
Random page
Help
Categories
LemonWiki共筆
Search
Search
Appearance
Log in
Personal tools
Log in
Pages for logged out editors
learn more
Contributions
Talk
Editing
Troubleshooting of MySQL errors
(section)
Page
Discussion
English
Read
Edit
View history
Tools
Tools
move to sidebar
hide
Actions
Read
Edit
View history
General
What links here
Related changes
Special pages
Page information
Appearance
move to sidebar
hide
Warning:
You are not logged in. Your IP address will be publicly visible if you make any edits. If you
log in
or
create an account
, your edits will be attributed to your username, along with other benefits.
Anti-spam check. Do
not
fill this in!
=== Table already exists when using RENAME TABLE === Situation: When replacing a table across databases using <code>RENAME TABLE</code>, the following error occurs even after renaming the existing table as a backup: <pre lang="text"> Table '.\DATABASE_NAME\TABLE_NAME' already exists </pre> Possible cause: The source and destination tables have the same foreign key constraint name. Renaming the existing table leaves its custom constraint name unchanged, causing a naming conflict when the new table is moved into the destination database. MySQL 5.7, 8.0, and 8.4 require foreign key constraint names to be unique within a database. See [https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/create-table-foreign-keys.html MySQL Foreign Key Constraints]. Solution: '''1. Confirm that the backup table name is available, and check the foreign key names on both tables.''' <pre lang="sql"> SHOW CREATE TABLE target_db.example_items; SHOW CREATE TABLE staging_db.example_items; </pre> Look for <code>FOREIGN KEY</code> in the output. For example: <pre lang="sql"> KEY `ix_example_parent_id` (`parent_id`), CONSTRAINT `fk_example_parent` FOREIGN KEY (`parent_id`) REFERENCES `example_parents` (`id`) ON DELETE SET NULL </pre> <table> <tr><th>Syntax</th><th>Meaning</th></tr> <tr><td><code>KEY ...</code></td><td>An index; '''this alone does not indicate a foreign key'''</td></tr> <tr><td><code>CONSTRAINT fk_example_parent</code></td><td>The foreign key constraint name, used when dropping the foreign key</td></tr> <tr><td><code>FOREIGN KEY (parent_id)</code></td><td>The foreign key column in this table</td></tr> <tr><td><code>REFERENCES example_parents (id)</code></td><td>The referenced parent table and column</td></tr> <tr><td><code>ON DELETE SET NULL</code></td><td>Sets the corresponding foreign key value to <code>NULL</code> when the referenced parent row is deleted</td></tr> </table> An <code>fk_</code> prefix alone does not identify a foreign key. Look for <code>FOREIGN KEY (...) REFERENCES ...</code> to confirm. Also, <code>SHOW CREATE TABLE</code> lists only foreign keys defined on that table. It does not list foreign keys in other tables that reference it. '''2. If the foreign key constraint is unnecessary, drop the conflicting constraint from the existing destination table, then swap the tables.''' <pre lang="sql"> ALTER TABLE target_db.example_items DROP FOREIGN KEY fk_example_parent; RENAME TABLE target_db.example_items TO target_db.example_items_backup, staging_db.example_items TO target_db.example_items; </pre> Notes: * Dropping the foreign key preserves the column and index, but removes referential integrity checks and the original <code>ON DELETE SET NULL</code> behavior. * If the foreign key is required, recreate the constraint with a different name. * After the swap, verify which database the foreign key references. Foreign keys in other tables follow the renamed old table; they do not automatically switch to the replacement table. See [https://docs.percona.com/percona-toolkit/pt-online-schema-change.html Percona’s Foreign Key Handling Documentation].
Summary:
Please note that all contributions to LemonWiki共筆 are considered to be released under the Creative Commons Attribution-NonCommercial-ShareAlike (see
LemonWiki共筆:Copyrights
for details). If you do not want your writing to be edited mercilessly and redistributed at will, then do not submit it here.
You are also promising us that you wrote this yourself, or copied it from a public domain or similar free resource.
Do not submit copyrighted work without permission!
Cancel
Editing help
(opens in new window)