Rename operations adalah perubahan struktural yang bisa berdampak besar pada aplikasi. Setelah melakukan refactoring database untuk beberapa migrations, saya akan berbagi procedures yang aman dan terstruktur.

Rename Tabel

1. Rename Single Table

-- Rename tabel
RENAME TABLE old_table TO new_table;
-- Atau dengan ALTER
ALTER TABLE old_table RENAME TO new_table;

2. Rename Multiple Tables

-- Rename multiple tables sekaligus
RENAME TABLE 
    old_table1 TO new_table1,
    old_table2 TO new_table2,
    old_table3 TO new_table3;

3. Move Table ke Database Lain

-- Pindahkan tabel ke database berbeda
RENAME TABLE old_db.old_table TO new_db.new_table;

4. Atomic Rename (Swap Tables)

-- Swap dua tables (berguna untuk maintenance)
RENAME TABLE 
    users TO users_old,
    users_new TO users;

Rename Column

1. Rename Single Column

-- MySQL 8.0+
ALTER TABLE table_name 
RENAME COLUMN old_column TO new_column;
-- Semua versi
ALTER TABLE table_name 
CHANGE COLUMN old_column new_column VARCHAR(100);
-- Dengan modify type juga
ALTER TABLE table_name 
CHANGE COLUMN old_name new_name VARCHAR(50) NOT NULL;

2. Rename Multiple Columns

-- Multiple ALTER statements
ALTER TABLE users 
RENAME COLUMN first_name TO fname,
RENAME COLUMN last_name TO lname;

Rename Database

1. Method 1: Dump dan Restore (Recommended)

# 1. Backup database lama
mysqldump old_database > old_database_backup.sql
2. Create database baru
mysql -e "CREATE DATABASE new_database;"
3. Restore ke database baru
mysql new_database < old_database_backup.sql
4. Verify
mysql -e "SHOW TABLES FROM new_database;"
5. Drop database lama (setelah verify)
mysql -e "DROP DATABASE old_database;"

2. Method 2: Rename All Tables

-- Rename semua tables ke database baru
RENAME TABLE 
    old_db.table1 TO new_db.table1,
    old_db.table2 TO new_db.table2,
    old_db.table3 TO new_db.table3;
-- Drop database kosong
DROP DATABASE old_db;

Pre-Rename Checklist

1. Backup

# Selalu backup sebelum rename
mysqldump database_name > backup_before_rename.sql

2. Check Dependencies

-- Cek foreign keys
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'database_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;
-- Cek stored procedures dan views
SHOW CREATE VIEW view_name;
SHOW CREATE PROCEDURE procedure_name;

3. Application Updates

# Update application code
# - SQL queries
# - ORM models
# - Migration scripts
Search dalam codebase
grep -r "old_table_name" /path/to/application/

Safe Rename Script

#!/bin/bash
# safe_rename_table.sh
DB_NAME="$1"
OLD_NAME="$2"
NEW_NAME="$3"
if [ -z "$DB_NAME" ] || [ -z "$OLD_NAME" ] || [ -z "$NEW_NAME" ]; then
echo "Usage: $0 <database> <old_table> <new_table>"
exit 1
fi
echo "=== SAFE TABLE RENAME ==="
echo "Database: $DB_NAME"
echo "From: $OLD_NAME"
echo "To: $NEW_NAME"
1. Backup
echo "Creating backup..."
mysqldump "$DB_NAME" "$OLD_NAME" > "/tmp/${OLD_NAME}
backup$(date +%s).sql"
2. Verify table exists
TABLE_EXISTS=$(mysql -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$DB_NAME' AND TABLE_NAME = '$OLD_NAME';" | tail -1)
if [ "$TABLE_EXISTS" -eq 0 ]; then
echo "Table $OLD_NAME does not exist!"
exit 1
fi
3. Check for dependencies
echo "Checking dependencies..."
DEPS=$(mysql -e "SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '$OLD_NAME';" | tail -1)
if [ "$DEPS" -gt 0 ]; then
echo "WARNING: Table has $DEPS foreign key dependencies!"
read -p "Continue anyway? (yes/no): " confirm
if [ "$confirm" != "yes" ]; then
exit 1
fi
fi
4. Perform rename
echo "Renaming table..."
mysql -e "RENAME TABLE `$DB_NAME`.`$OLD_NAME` TO `$DB_NAME`.`$NEW_NAME`;"
if [ $? -eq 0 ]; then
echo "Rename successful!"
echo "Backup: /tmp/${OLD_NAME}
backup$(date +%s).sql"
else
echo "Rename failed!"
exit 1
fi

Handling Errors

1. Rename Fails

-- Jika rename gagal karena foreign key
-- 1. Drop FK constraint
ALTER TABLE child_table DROP FOREIGN KEY fk_constraint;
-- 2. Rename
RENAME TABLE old_table TO new_table;
-- 3. Recreate FK dengan nama baru
ALTER TABLE child_table 
ADD CONSTRAINT fk_new_table 
FOREIGN KEY (parent_id) REFERENCES new_table(id);

2. Column Rename dengan Views

-- Jika column digunakan dalam views
-- 1. Drop views
DROP VIEW view_name;
-- 2. Rename column
ALTER TABLE table_name RENAME COLUMN old TO new;
-- 3. Recreate views
CREATE VIEW view_name AS SELECT new_column FROM table_name;

Best Practices

1. Always backup: Sebelum rename operations
2. Test di development: Sebelum production
3. Update application: Semua references
4. Document changes: Track refactoring
5. Monitor errors: Setelah rename
6. Plan rollback: Jika ada issues

Kesimpulan

Rename operations memerlukan planning:
– Backup: Selalu backup sebelum changes
– Dependencies: Check FKs, views, procedures
– Application: Update semua code references
– Verification: Test setelah rename

Dengan proper procedures, rename bisa dilakukan dengan aman.