SQL - Alter Tables

SQL ALTER TABLE Command

Introduction to ALTER TABLE

The ALTER TABLE statement is a Data Definition Language (DDL) command used to modify the structure of an existing table. You can use it to add, drop, or modify columns, as well as add or remove constraints like primary keys, foreign keys, and unique constraints.

Warning: Modifying the structure of a large production table can be a slow and resource-intensive operation. It may lock the table, preventing reads and writes, causing application downtime. Always test schema changes in a development environment first and plan for potential downtime.

Adding and Dropping Columns

ADD COLUMN

This adds a new column to a table. The syntax is fairly standard across databases.

-- Basic syntax
ALTER TABLE table_name ADD COLUMN new_column_name data_type [constraints];

-- Example: Add a contact_email column to the Customers table.
ALTER TABLE Customers ADD COLUMN contact_email VARCHAR(255);

-- Example: Add a column with a default value to avoid NULLs in existing rows.
ALTER TABLE Customers ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

DROP COLUMN

This permanently removes a column and all of its data. This action is irreversible.

-- Basic syntax
ALTER TABLE table_name DROP COLUMN column_name;

-- Example: Remove the now-unused 'sex' column.
ALTER TABLE Customers DROP COLUMN sex;

Modifying Columns (Data Type, Rename)

The syntax for modifying columns varies significantly between database systems.

Change Data Type

This changes the data type of an existing column. Be careful, as this can lead to data truncation if the new type is more restrictive than the old one (e.g., VARCHAR(100) to VARCHAR(50)).

-- PostgreSQL and SQL Server
ALTER TABLE Customers ALTER COLUMN address TYPE TEXT; -- PostgreSQL
ALTER TABLE Customers ALTER COLUMN address NVARCHAR(MAX); -- SQL Server

-- MySQL and Oracle
ALTER TABLE Customers MODIFY COLUMN address TEXT;

Rename Column

This changes the name of a column.

-- Standard SQL / PostgreSQL
ALTER TABLE Customers RENAME COLUMN name TO full_name;

-- MySQL
ALTER TABLE Customers RENAME COLUMN name TO full_name; -- Modern MySQL
-- Or the older, more versatile CHANGE COLUMN syntax:
ALTER TABLE Customers CHANGE COLUMN name full_name VARCHAR(100) NOT NULL;

-- SQL Server (uses a stored procedure)
EXEC sp_rename 'Customers.name', 'full_name', 'COLUMN';

Working with Constraints (ADD, DROP)

You can add or remove constraints like PRIMARY KEY, UNIQUE, and FOREIGN KEY after a table has been created. It is a best practice to name your constraints explicitly.

ADD CONSTRAINT

-- Example: Add a UNIQUE constraint to the email column.
ALTER TABLE Customers
ADD CONSTRAINT uq_customers_email UNIQUE(contact_email);

-- Example: Add a FOREIGN KEY constraint.
ALTER TABLE Orders
ADD CONSTRAINT fk_orders_customer_id
FOREIGN KEY (customer_id) REFERENCES Customers(id);

DROP CONSTRAINT

Dropping a constraint requires knowing its name. This is why naming them is so important.

-- Standard SQL / PostgreSQL / SQL Server
ALTER TABLE Customers DROP CONSTRAINT uq_customers_email;

-- MySQL
-- For a UNIQUE constraint:
ALTER TABLE Customers DROP INDEX uq_customers_email;
-- For a FOREIGN KEY constraint:
ALTER TABLE Orders DROP FOREIGN KEY fk_orders_customer_id;

Safety, Performance, and Best Practices

Modifying tables, especially large ones, is a high-risk operation. Follow these guidelines:

• Use Transactions: In databases that support transactional DDL (like PostgreSQL), wrap your ALTER statement in a BEGIN...COMMIT block. If something goes wrong, you can ROLLBACK the change.
• Backup First: Always ensure you have a recent, restorable backup before performing major schema changes on a production database.
• Schedule Downtime: Plan to run these changes during a maintenance window with low user traffic to minimize impact.
• Use Online Schema Change Tools: For very large tables on systems like MySQL, a standard ALTER TABLE can lock the table for hours. Tools like gh-ost or pt-online-schema-change perform the migration with minimal locking, allowing the application to remain online.
• Test, Test, Test: Run the exact migration script on a staging server that mirrors your production environment to identify any potential issues or performance problems before running it on production.