3

I'm going to rename a MySQL 8.0 table via Flyway SQL script:

RENAME TABLE aaa TO bbb;

and it's supposed to be run on env many times - but such the script is not idempotent, trying to run it second time I catch an error:

SQL State  : 42S01
Error Code : 1050
Message    : Table 'bbb' already exists
Location   : db/migration/V7__blabla.sql (/usr/local/...)
Line       : 1
Statement  : RENAME TABLE aaa TO bbb

Could someone suggest please how to handle it?

Many thanks in advance!

Dmitry Adonin
  • 1,064
  • 3
  • 16
  • 36

2 Answers2

4

IF EXISTS

Use the IF EXISTS clause. The command ceases, with no effect, if no such table is found.

ALTER TABLE IF EXISTS name
RENAME TO new_name 
;

This works in Postgres. For MySQL, see this Question.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • Seems like it's not a valid syntax for MySQL unfortunately... Thank you for your quick response! – Dmitry Adonin Apr 08 '21 at 17:01
  • 1
    The principle is correct, just a minor syntax tweak. See https://stackoverflow.com/questions/16837134/alter-table-if-exists-or-create-if-doesnt – Julia Hayward Apr 12 '21 at 15:40
1

you should think about multiple cases

  1. Renaming a table referenced by a view
  2. Renaming a table referenced by a stored procedure
  3. Renaming a table that has foreign keys referenced to

this link will help users safely rename your table. https://www.mysqltutorial.org/mysql-rename-table/

Mohsen Bahaloo
  • 257
  • 2
  • 2