ALTER TABLE

The ALTER TABLE statement is used to modify an existing table in a database. It can be used to add, delete, or modify columns; add or drop constraints; and rename a table.

Syntax

The basic syntax for ALTER TABLE varies slightly depending on the specific SQL dialect (e.g., SQL Server, MySQL, PostgreSQL, Oracle), but the core operations are similar.

General structure:

ALTER TABLE table_name
    <action>;

Where <action> can be one or more of the following:

ADD column_name datatype [constraints]
DROP COLUMN column_name
ALTER COLUMN column_name datatype [constraints] (Syntax varies greatly by RDBMS)
ADD CONSTRAINT constraint_name constraint_definition
DROP CONSTRAINT constraint_name
RENAME TO new_table_name (Syntax varies greatly by RDBMS)

Common Operations

1. Adding a Column

You can add a new column to an existing table. You can also specify data type and constraints for the new column.

ALTER TABLE Customers
ADD Email VARCHAR(255);

ALTER TABLE Products
ADD Price DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

2. Dropping a Column

You can remove a column from a table. Be cautious, as this will permanently delete all data in that column.

ALTER TABLE Orders
DROP COLUMN OrderDate;

3. Modifying a Column

This operation allows you to change the data type, size, or constraints of an existing column. The exact syntax varies significantly between database systems.

SQL Server Example:

ALTER TABLE Employees
ALTER COLUMN PhoneNumber VARCHAR(20);

MySQL Example:

ALTER TABLE Employees
MODIFY COLUMN PhoneNumber VARCHAR(20);

PostgreSQL Example:

ALTER TABLE Employees
ALTER COLUMN PhoneNumber TYPE VARCHAR(20);

4. Adding a Constraint

You can add constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK to a table.

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerID
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

ALTER TABLE Products
ADD CONSTRAINT UQ_ProductName
UNIQUE (ProductName);

5. Dropping a Constraint

You can remove an existing constraint from a table.

ALTER TABLE Products
DROP CONSTRAINT UQ_ProductName;

6. Renaming a Table

This operation allows you to change the name of a table. Again, the syntax can differ across RDBMS.

SQL Server / PostgreSQL Example:

ALTER TABLE OldTableName
RENAME TO NewTableName;