SQL ALTER TABLE Statement

The ALTER TABLE statement in SQL is used to modify the structure of an existing table. You can use it to add, delete, or modify columns in a table, as well as to add or drop constraints.

Basic Syntax

1. Add a Column

   ALTER TABLE table_name
   ADD column_name column_type;
   

2. Drop a Column

   ALTER TABLE table_name
   DROP COLUMN column_name;
   

3. Modify a Column

   ALTER TABLE table_name
   MODIFY column_name new_column_type;
   

4. Add a Constraint

   ALTER TABLE table_name
   ADD CONSTRAINT constraint_name constraint_type (column_name);
   

5. Drop a Constraint

   ALTER TABLE table_name
   DROP CONSTRAINT constraint_name;
   

Example

Consider a table named employees with the following structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

1. Add a New Column

To add a new column hire_date of type DATE:

ALTER TABLE employees
ADD hire_date DATE;

Output:

The employees table will now have an additional column hire_date.

2. Drop a Column

To remove the salary column:

ALTER TABLE employees
DROP COLUMN salary;

Output:

The employees table will no longer have the salary column.

3. Modify a Column

To change the name column to be of type TEXT:

ALTER TABLE employees
MODIFY name TEXT;

Output:

The name column's data type will be updated to TEXT.

4. Add a Constraint

To add a UNIQUE constraint to the name column:

ALTER TABLE employees
ADD CONSTRAINT unique_name UNIQUE (name);

Output:

The name column will now have a UNIQUE constraint ensuring that all values in the name column are distinct.

5. Drop a Constraint

To remove the UNIQUE constraint from the name column:

ALTER TABLE employees
DROP CONSTRAINT unique_name;

Output:

The UNIQUE constraint on the name column will be removed.

These operations help in evolving the database schema as the requirements change.