SQL ALTER TABLE Statement: Syntax, Usage, and Examples

The ALTER TABLE statement allows you to change the structure of an existing table without recreating it. You can use it to add, delete, rename, or modify columns and constraints. ALTER TABLE is one of the most important tools for maintaining and evolving your database schema over time.

How to Use ALTER TABLE

The basic syntax for altering a table is:

ALTER TABLE table_name
operation;

The operation might involve adding a column, renaming a column, modifying a data type, or changing constraints.

Example: Add a New Column

ALTER TABLE employees
ADD department_id INT;

This adds a new department_id column to the employees table.

When to Use ALTER TABLE SQL

ALTER TABLE is used when:

• Adding new columns after the table has been created
• Updating data types of existing columns
• Renaming columns for clarity or consistency
• Adding constraints like UNIQUE, DEFAULT, or CHECK
• Changing nullability of fields
• Creating or removing foreign key relationships

These changes are essential during database updates, feature rollouts, and application iterations.

ALTER TABLE Examples

Example 1: Add a Column

ALTER TABLE orders
ADD order_status VARCHAR(20) DEFAULT 'Pending';

This adds a order_status column with a default value.

Example 2: Add Multiple Columns

You can use ALTER TABLE in a single statement:

ALTER TABLE customers
ADD (
  loyalty_points INT DEFAULT 0,
  referred_by INT
);

This is efficient when expanding table functionality.

Example 3: Modify a Column's Data Type

ALTER TABLE products
MODIFY price DECIMAL(10, 2);

Use ALTER TABLE and MODIFY together to adjust precision or scale for numeric fields.

Note: Syntax may differ slightly between databases. In SQL Server, use ALTER COLUMN instead of MODIFY.

Example 4: Rename a Column

-- MySQL
ALTER TABLE employees
RENAME COLUMN fullname TO full_name;

Renaming columns improves schema clarity. ALTER TABLE syntax varies by system:

• MySQL: RENAME COLUMN
• PostgreSQL: RENAME COLUMN
• SQL Server: sp_rename

Example 5: Change Column Nullability

ALTER TABLE users
MODIFY email VARCHAR(255) NOT NULL;

This makes the email field required.

In SQL Server:

ALTER TABLE users
ALTER COLUMN email VARCHAR(255) NOT NULL;

Example 6: Drop a Column

ALTER TABLE invoices
DROP COLUMN discount;

Remove columns that are no longer used in the application.

Example 7: Add a Primary Key

ALTER TABLE departments
ADD PRIMARY KEY (dept_id);

You can also define composite keys when needed.

Example 8: Add a Foreign Key

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(dept_id);

This enforces referential integrity.

Example 9: Drop a Constraint

ALTER TABLE orders
DROP CONSTRAINT fk_customer;

Used during restructuring or when removing relationships.

Learn More About ALTER TABLE

SQL ALTER TABLE Add Column

Adding a column dynamically is one of the most common uses:

ALTER TABLE products
ADD weight DECIMAL(5, 2);

This operation is usually fast and non-disruptive, but be cautious when dealing with large datasets or nullable columns.

ALTER TABLE Query to Add Multiple Columns

ALTER TABLE events
ADD (
  location VARCHAR(100),
  event_type VARCHAR(50)
);

This pattern is useful when rolling out several new features or data fields at once.

ALTER TABLE Modify Column

Changing the data type or length of an existing column allows you to adapt to new data requirements:

ALTER TABLE employees
MODIFY last_name VARCHAR(100);

Or change a column from NULL to NOT NULL:

ALTER TABLE employees
MODIFY phone_number VARCHAR(15) NOT NULL;

In SQL Server:

ALTER TABLE employees
ALTER COLUMN phone_number VARCHAR(15) NOT NULL;

Always make sure existing data is compatible with the new definition.

ALTER TABLE Rename Column

Renaming columns often improves readability or aligns with naming conventions:

ALTER TABLE accounts
RENAME COLUMN acc_number TO account_number;

In SQL Server:

EXEC sp_rename 'accounts.acc_number', 'account_number', 'COLUMN';

Be mindful that renaming may break existing queries, views, or stored procedures.

ALTER TABLE Add Default

ALTER TABLE users
ADD CONSTRAINT default_country
DEFAULT 'USA' FOR country;

This sets a default for a column if no value is provided during insert.

ALTER TABLE with Index

You can also create indexes through ALTER TABLE:

ALTER TABLE orders
ADD INDEX idx_order_date (order_date);

Improves query performance on large tables.

ALTER TABLE Add Unique Constraint

ALTER TABLE customers
ADD CONSTRAINT unique_email UNIQUE (email);

Ensures no two customers share the same email.

Altering Temporary Tables

You can also modify temporary tables:

CREATE TEMP TABLE temp_results (
  id INT
);

ALTER TABLE temp_results
ADD result_score INT;

Temporary tables behave just like regular tables in terms of structure modification.

Best Practices for Using ALTER TABLE in SQL

1. Use on Non-Peak Hours: Structural changes on large tables can lock the table.
2. Backup First: Before altering important tables, back up the data.
3. Test in Dev: Apply and test ALTER TABLE queries in development or staging environments first.
4. Document Changes: Keep schema change logs or use version control tools for schema management.
5. Be Aware of Engine-Specific Differences: MODIFY, RENAME, and constraint syntax can differ between MySQL, SQL Server, PostgreSQL, and Oracle.