SQL Alter Table

The ALTER TABLE statement is a Data Definition Language (DDL) command used to modify the structural definition of an existing table. It allows you to alter a table's schema without having to drop and recreate the entire table or lose stored data rows.

Common operations include adding new columns, dropping existing columns, modifying column data types or sizes, renaming columns or tables, and adding or removing table constraints.

Basic Syntax

ALTER TABLE table_name
action_to_perform;

Core Operations & Examples

1. Adding a Column (ADD)

To append a new column to an existing table:

-- General Syntax
ALTER TABLE table_name
ADD column_name data_type constraint;

-- Example: Add a birthdate column to the Customers table
ALTER TABLE Customers
ADD BirthDate DATE;

• Default Values on Addition: Adding a column defined as NOT NULL requires providing a DEFAULT value if the table already contains rows.
• Engine Variations:
• MySQL: Supports positioning keywords like FIRST or AFTER column_name (e.g., ADD Phone VARCHAR(20) AFTER Email;).
• SQL Server / PostgreSQL: New columns are automatically appended to the end of the existing column list.

2. Dropping a Column (DROP COLUMN)

To permanently remove a column and all data stored within it:

-- General Syntax
ALTER TABLE table_name
DROP COLUMN column_name;

-- Example: Remove the MiddleName column from Employees
ALTER TABLE Employees
DROP COLUMN MiddleName;

Warning: Dropping a column deletes all data in that column across all rows. If the column is referenced by indexes, foreign keys, or check constraints, those dependencies must be dropped first.

3. Modifying Column Data Type or Size

To adjust the data type, character length, or nullability of an existing column:

Syntax varies slightly across RDBMS engines:

-- PostgreSQL / Oracle
ALTER TABLE Customers
ALTER COLUMN Email TYPE VARCHAR(150);

-- SQL Server (T-SQL)
ALTER TABLE Customers
ALTER COLUMN Email VARCHAR(150) NOT NULL;

-- MySQL / MariaDB
ALTER TABLE Customers
MODIFY COLUMN Email VARCHAR(150) NOT NULL;

4. Renaming a Column

Syntax for renaming an individual column:

-- PostgreSQL / Oracle / MySQL 8.0+
ALTER TABLE Customers
RENAME COLUMN Phone TO PhoneNumber;

-- SQL Server (Uses built-in stored procedure instead of standard DDL)
EXEC sp_rename 'Customers.Phone', 'PhoneNumber', 'COLUMN';

5. Renaming a Table

To rename the entire table structure within the database:

-- PostgreSQL / MySQL / Oracle
ALTER TABLE Customers
RENAME TO ClientAccounts;

-- SQL Server
EXEC sp_rename 'Customers', 'ClientAccounts';

Managing Constraints with ALTER TABLE

You can use ALTER TABLE to enforce or remove relational constraints dynamically on active tables.

Adding Constraints

-- Add a Foreign Key Constraint
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

-- Add a UNIQUE Constraint
ALTER TABLE Employees
ADD CONSTRAINT UQ_Employee_SSN UNIQUE (SSN);

-- Add a CHECK Constraint
ALTER TABLE Products
ADD CONSTRAINT CHK_Product_Price CHECK (Price > 0);

Dropping Constraints

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE Orders
DROP CONSTRAINT FK_Orders_Customers;

-- MySQL (Requires specific syntax for Foreign Keys and Indexes)
ALTER TABLE Orders
DROP FOREIGN KEY FK_Orders_Customers;

Operational Summary Reference