Renaming a table

Last modified: March 28, 2025

When you rename a MySQL table, most comparison tools consider this as the deletion or recreation of a new table. In this case, the deployment will lead to data loss of the renamed table.

Solution: To avoid data loss when renaming a table, you should create a migration script to rename the table using the sp_rename stored procedure.

Note

The comparison engine of the tools doesn’t handle all cases automatically, which is why the cases mentioned above must be resolved manually.

The screenshot displays the Schema Comparison document and the scripts generated by Schema Compare Engine and a migration script, which should be created manually to perform errorless data migration.

The table provides source and target table script examples to illustrate the case:

CREATE TABLE StoreProduct (
  ProductNumber INT PRIMARY KEY,
  ProductPrice DECIMAL(5, 2),
  ProductName VARCHAR(25) NULL
);

CREATE TABLE Product (
  ProductNumber INT PRIMARY KEY,
  ProductPrice DECIMAL(5, 2),
  ProductName VARCHAR(25) NULL
);

INSERT INTO Product (ProductNumber, ProductPrice, ProductName)
  VALUES (1125, 234.56, 'screwdriver');
INSERT INTO Product (ProductNumber, ProductPrice, ProductName)
  VALUES (1126, 89.71, 'hummer');

The table provides examples of scripts generated by the Schema Comparison engine and those that should be generated manually for errorless data migration to illustrate the case:

DROP TABLE product;

CREATE TABLE storeproduct (
  ProductNumber INT NOT NULL,
  ProductPrice DECIMAL(5, 2) DEFAULT NULL,
  ProductName VARCHAR(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (ProductNumber)
)
ENGINE = INNODB,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

-- migration script:

ALTER TABLE Product RENAME TO StoreProduct;

-- migration script.