ALTER TABLE Statement in SQL

11/22/2025
All Articles

SQL ALTER TABLE tutorial with examples for beginners

ALTER TABLE Statement in SQL

ALTER TABLE Statement in SQL – SQL Tutorial for Beginners

The ALTER TABLE statement in SQL is used to modify the structure of an existing table. It allows you to add, remove, or change columns, as well as manage constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and DEFAULT.

This SQL tutorial will help you understand:

  • What ALTER TABLE does

  • Different ALTER TABLE operations

  • Add, modify, and drop columns

  • Add and remove constraints

  • Rename tables and columns

  • Real-world examples

  • Best practices

🔹 What Is ALTER TABLE?

The ALTER TABLE command updates a table’s schema without deleting existing data.

You can use ALTER TABLE to:

  • Add a new column

  • Modify a column

  • Delete a column

  • Rename a column or table

  • Add/remove constraints

  • Change data types

🔸 Basic Syntax

ALTER TABLE table_name
operation;

🔹 1️⃣ Add a New Column

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE users
ADD age INT;

🔹 2️⃣ Add Multiple Columns

ALTER TABLE users
ADD (phone VARCHAR(20), status VARCHAR(20));

🔹 3️⃣ Modify Column Data Type

ALTER TABLE users
MODIFY age SMALLINT;

🔹 4️⃣ Change Column Name

MySQL:

ALTER TABLE users
CHANGE COLUMN name full_name VARCHAR(150);

PostgreSQL:

ALTER TABLE users
RENAME COLUMN name TO full_name;

🔹 5️⃣ Drop (Delete) a Column

ALTER TABLE users
DROP COLUMN age;

⚠ Warning: Dropped columns cannot be recovered without backup.

🔹 6️⃣ Add PRIMARY KEY

ALTER TABLE customers
ADD PRIMARY KEY (customer_id);

🔹 7️⃣ Add FOREIGN KEY

ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);

🔹 8️⃣ Drop FOREIGN KEY

MySQL:

ALTER TABLE orders
DROP FOREIGN KEY fk_user;

🔹 9️⃣ Add UNIQUE Constraint

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE(email);

🔹 🔟 Remove UNIQUE Constraint

MySQL:

ALTER TABLE users
DROP INDEX unique_email;

🔹 1️⃣1️⃣ Add NOT NULL Constraint

ALTER TABLE employees
MODIFY name VARCHAR(150) NOT NULL;

🔹 1️⃣2️⃣ Add DEFAULT Value

ALTER TABLE accounts
ALTER status SET DEFAULT 'active';

MySQL alternative:

ALTER TABLE accounts
MODIFY status VARCHAR(20) DEFAULT 'active';

🔹 1️⃣3️⃣ Rename a Table

ALTER TABLE users
RENAME TO customers;

Real-World Examples

✔ Add a last_login column to track user activity

ALTER TABLE users
ADD last_login DATETIME;

✔ Update salary column to DECIMAL type

ALTER TABLE employees
MODIFY salary DECIMAL(10,2);

✔ Add a foreign key to connect orders to customers

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

✔ Drop an unnecessary column

ALTER TABLE products
DROP COLUMN old_price;

Best Practices

✔ Backup tables before structural changes
✔ Use constraints carefully to maintain data integrity
✔ Avoid dropping columns unless necessary
✔ Test ALTER commands on a development database first
✔ Keep table naming consistent
✔ Add indexes for improved query performance

Summary

In this SQL ALTER TABLE tutorial, you learned:

  • How to add, modify, and drop columns

  • How to manage constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY)

  • How to rename tables and columns

  • Real-world ALTER TABLE examples

ALTER TABLE is essential for maintaining and evolving database structure without losing data.

Article