SQL ALTER Statement

Introduction

The ALTER statement allows you to modify existing database objects such as tables, columns, and constraints. It is a critical tool for evolving your schema over time without losing data. Whether adding new columns, changing data types, renaming tables, or updating constraints, ALTER empowers you to adapt your structure as requirements change.

1. Adding a Column

To add a new column to an existing table, use ALTER TABLE ... ADD.
Syntax:

ALTER TABLE table_name ADD column_name data_type [DEFAULT default_value] [NOT NULL];

Example:

ALTER TABLE employees
ADD department VARCHAR(50) DEFAULT 'General';

This adds the department column with a default of 'General'. Existing rows will receive this default.

2. Dropping a Column

To remove an unwanted column, use ALTER TABLE ... DROP COLUMN.
Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE employees
DROP COLUMN department;

This permanently deletes the department column and its data.

3. Modifying a Column

You can change a column’s data type, size, or nullability using ALTER TABLE ... ALTER (or MODIFY).
MySQL Syntax:

ALTER TABLE employees
MODIFY salary DECIMAL(12,2) NOT NULL;

PostgreSQL Syntax:

ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC(12,2),
ALTER COLUMN salary SET NOT NULL;

This example expands the precision of salary and enforces it cannot be NULL.

4. Renaming a Column or Table

To rename objects, databases provide dialect-specific syntax.
PostgreSQL:

ALTER TABLE employees RENAME COLUMN first_name TO fname;

ALTER TABLE employees RENAME TO staff;

MySQL:

ALTER TABLE employees RENAME COLUMN first_name TO fname;
-- or
ALTER TABLE employees CHANGE first_name fname VARCHAR(50);

Renaming helps clarify schema semantics as naming conventions evolve.

5. Adding and Dropping Constraints

Constraints ensure data integrity. You can add or drop them via ALTER TABLE.
Add Primary Key:

ALTER TABLE orders
ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);

Drop Primary Key (MySQL):

ALTER TABLE orders DROP PRIMARY KEY;

Add Foreign Key:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE;

Drop Foreign Key (MySQL):

ALTER TABLE orders DROP FOREIGN KEY fk_orders_customers;

These changes enforce or remove referential rules on the fly.

6. Renaming Indexes

Indexes accelerate queries. You can drop and recreate, or rename.
PostgreSQL:

ALTER INDEX idx_old_name RENAME TO idx_new_name;

MySQL: must recreate:

DROP INDEX idx_old_name ON orders;
CREATE INDEX idx_new_name ON orders(customer_id);

Renaming helps maintain meaningful index naming conventions.

7. Altering Schemas and Other Objects

You can move tables between schemas or change ownership.
PostgreSQL Move Table:

ALTER TABLE public.orders SET SCHEMA sales;

Change Owner:

ALTER TABLE orders OWNER TO new_owner;

These commands help reorganize and secure database objects.

8. Best Practices

When using ALTER:
• Always back up data or run changes in a transaction.
• Test on a staging environment before production.
• Minimize downtime by batching multiple alterations.
• Consider locking and performance impacts of large schema changes.

Summary

The ALTER statement is indispensable for evolving your database schema. From adding columns and constraints to renaming and moving objects, mastering ALTER ensures your system adapts smoothly as requirements change.