SQL ALTER TABLE Statement

The SQL ALTER TABLE statement is used to change the structure of an existing table. It helps to add, change, delete a column in a table.

The ALTER TABLE is also used to add and remove constraints on an existing table.

Demo table

Let us consider that we have a table called "Employee" with the following structure:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| first_name | varchar(30)   | NO   |     | NULL    |       |
| last_name  | varchar(30)   | NO   |     | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
| salary     | decimal(12,4) | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

You can check the structure of a table by running the following command:

DESC employee;
-- or
DESCRIBE employee

ALTER TABLE - ADD Column

The syntax for adding a new column to an existing table can be given as follows:

ALTER TABLE table_name 
    ADD column_name data_type constraints;

The following SQL statement adds a new column "email" to the "Employee" table:

ALTER TABLE employee ADD email VARCHAR(150);

Now, after running the above command, if you check the structure of the "Employee" table using the DESC employee, you will get the following output:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| first_name | varchar(30)   | NO   |     | NULL    |       |
| last_name  | varchar(30)   | NO   |     | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
| salary     | decimal(12,4) | NO   |     | NULL    |       |
| email      | varchar(150)  | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

As you can see, the new "email" column has been added at the end of the "Employee" table.

ALTER TABLE - ADD Multiple Columns

You can also add multiple columns to an existing table using the following syntax:

ALTER TABLE table_name 
    ADD ( column1_name data_type constraints,
            column2_name data_type constraints,
            ...
            columnN_name data_type constraints );

The following SQL statement will add more than one column to the "Employee" table:

ALTER TABLE employee
    ADD ( email VARCHAR(150)
          city VARCHAR(50) );

ALTER TABLE - MODIFY Column

The syntax for modifying a column in an existing table can be given as follows:

• For MySQL, MariaDB, Oracle:

  ALTER TABLE table_name
    MODIFY column_name column_type;
  

• For SQL Server:

  ALTER TABLE table_name
    MODIFY COLUMN column_name column_type;
  

• For PostgreSQL:

  ALTER TABLE table_name
    ALTER COLUMN column_name TYPE column_type;
  

Let us see an example of how to modify the "first_name" column of the "Employee" table. You can notice that different database systems have different syntax.

• For MySQL, MariaDB, Oracle:

  ALTER TABLE employee
    MODIFY first_name VARCHAR(40) NOT NULL;
  

• For SQL Server:

  ALTER TABLE employee
    MODIFY COLUMN first_name VARCHAR(40) NOT NULL;
  

• For PostgreSQL:

  ALTER TABLE employee
    ALTER COLUMN first_name TYPE VARCHAR(40) 
    ALTER COLUMN first_name SET NOT NULL;
  

ALTER TABLE - MODIFY Multiple Columns

You can also modify multiple columns in an existing table using the following syntax:

• For MySQL, MariaDB:

  ALTER TABLE table_name
    MODIFY column1_name column_type [ FIRST | AFTER column_name],
    MODIFY column1_name column_type [ FIRST | AFTER column_name],
    ...
  

• For Oracle:

  ALTER TABLE table_name
    MODIFY (  column1_name column_type,
              column2_name column_type,
              ...
        );
  

• For PostgreSQL:

  ALTER TABLE table_name
    ALTER COLUMN column1_name TYPE column_type, 
          ALTER COLUMN column2_name TYPE column_type, 
    ...
  

ALTER TABLE - DROP COLUMN

The syntax for removing a column in an existing table can be given as follows:

ALTER TABLE table_name
    DROP COLUMN column_name;

Note: Some database systems do not allow deleting a column.

The following SQL statement will remove the "salary" column from the "Employee" table:

ALTER TABLE employee
    DROP COLUMN salary;

ALTER TABLE - RENAME COLUMN

The syntax for renaming a column in an existing table can be given as follows:

• For MySQL, MariaDB:

  ALTER TABLE table_name
    CHANGE COLUMN old_name TO new_name;
  

• For Oracle, PostgreSQL:

  ALTER TABLE table_name 
    RENAME COLUMN old_name TO new_name;
  

• For SQL Server using the "sp_reanme" stored procedure.

  sp_rename 'table_name.old_column', 'new_name', 'COLUMN';
  

The following SQL statement will rename the column "salary" in the "Employee" table to "salary_by_month":

• For MySQL, MariaDB:

  ALTER TABLE employee
    CHANGE COLUMN salary TO salary_by_month decimal(12,4);
  

• For Oracle, PostgreSQL:

  ALTER TABLE employee
    RENAME COLUMN salary TO salary_by_month;
  

• For SQL Server using the "sp_reanme" stored procedure.

  sp_rename 'employee.salary', 'salary_by_month', 'COLUMN';
  

ALTER TABLE - RENAME Table

The syntax to rename a table can be given as follows:

• For MySQL, MariaDB, Oracle, PostgreSQL, SQLite:

  ALTER TABLE table_name
    RENAME TO new_table_name;
  

• For SQL Server using the "sp_reanme" stored procedure.

  sp_rename 'table_name', 'new_table_name';
  

The following SQL statement will rename the "Employee" table to "Employees" table:

• For MySQL, MariaDB, Oracle, PostgreSQL, SQLite:

  ALTER TABLE employee
    RENAME TO employees;
  

• For SQL Server using the "sp_reanme" stored procedure.

  sp_rename 'employee', 'employees';
  

ALTER TABLE - ADD Constraint

The syntax for adding a constraint in an existing table can be given as follows:

ALTER TABLE table_name 
    ADD constraint_name (column_name, ....);

The following SQL statement will add a UNIQUE constraint to the email column.

ALTER TABLE employee
    ADD UNIQUE (email);