Renaming Tables

Table names often need cleanup as requirements become clearer.

SQLite provides a direct and readable command for this.

Core Concepts

flowchart LR
  A[Old table name] --> B[ALTER TABLE old RENAME TO new]
  B --> C[New table name in schema]

Step	Command
Rename table	ALTER TABLE old_name RENAME TO new_name;
Verify names	.tables and .schema new_name

Code Examples

-- Example setup.
CREATE TABLE temp_customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

Expected output
Table temp_customers is created.

-- Rename table to final name.
ALTER TABLE temp_customers
RENAME TO customers;

Expected output
Table is renamed to customers.

-- Verify rename.
.tables
.schema customers

Expected output
customers appears; temp_customers no longer appears.

SQLite-Specific Nuances

SQLite Nuance

Modern SQLite versions update related schema references more reliably during rename operations than older versions.

Still, always verify dependent objects after renaming.

Common Pitfalls / Best Practices

Pitfall

Renaming a table while application code still uses the old name.

This causes immediate runtime errors.

Best Practice

Coordinate schema rename with application query updates in the same change set.

Quick Challenge

Rename old_orders to orders, then verify with .tables.

View Solution

ALTER TABLE old_orders
RENAME TO orders;

Expected output
.tables lists orders and does not list old_orders.