SQL Alter Table

SQL ALTER TABLE

The ALTER TABLE statement is used to modify the structure of an existing table. You can add, remove, or change columns and constraints.

Add a Column

ALTER TABLE customers
ADD phone VARCHAR(20);

Add a Column with NOT NULL

ALTER TABLE customers
ADD status VARCHAR(20) NOT NULL DEFAULT 'active';

Drop a Column

ALTER TABLE customers
DROP COLUMN phone;

Modify a Column (MySQL / MariaDB)

ALTER TABLE customers
MODIFY email VARCHAR(200);

Rename a Column (MySQL 8+)

ALTER TABLE customers
RENAME COLUMN name TO full_name;

Rename a Table

RENAME TABLE customers TO clients;

Add a Primary Key

ALTER TABLE customers
ADD PRIMARY KEY (id);

Add a Foreign Key

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

Drop a Constraint (MySQL)

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

Important Notes

• Altering large tables can lock the table temporarily
• Always back up before structural changes
• Syntax varies slightly between database systems

Common Mistakes

• Forgetting to specify data type when modifying columns
• Dropping columns that are used in foreign keys
• Running ALTER in production without testing

Next Step

Continue with SQL Constraints to understand rules like NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY in detail.