ALTER TABLE

The ALTER TABLE statement is used to modify an existing table in a database. You can use it to add, delete, or modify columns, and even change table constraints.

1. Add a Column

To add a new column to a table:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Employees
ADD DateOfBirth DATE;

This adds a new column DateOfBirth to the Employees table.

2. Delete a Column

To remove a column from a table:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE Employees
DROP COLUMN DateOfBirth;

This deletes the DateOfBirth column from the table.

3. Modify a Column

To change the datatype or size of an existing column:

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

Example:

ALTER TABLE Employees
MODIFY COLUMN LastName VARCHAR(100);

This changes the LastName column to allow up to 100 characters.

4. Rename a Column

To rename a column (syntax may vary by database):

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Example:

ALTER TABLE Employees
RENAME COLUMN LastName TO Surname;

This renames LastName to Surname.

5. Rename a Table

To rename the entire table:

ALTER TABLE old_table_name
RENAME TO new_table_name;

Example:

ALTER TABLE Employees
RENAME TO Staff;

This renames the Employees table to Staff.

6. Add a Constraint

You can add constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

Example:

ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

This sets EmployeeID as the primary key.

Key Points

• ALTER TABLE changes the table structure without deleting data.
• Always backup your table before making major changes.
• Syntax can vary slightly between MySQL, SQL Server, and Oracle.