Which statement is used to modify the structure of an existing table?

The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You should also use the ALTER TABLE command to add and drop various constraints on an existing table.

Syntax

The basic syntax of an ALTER TABLE command to DROP PRIMARY KEY constraint from a table is as follows.

This SQL tutorial explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with lots of clear, concise examples). We've also added some practice exercises that you can try for yourself.

Description

The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

To add a column in a table, the ALTER TABLE syntax in SQL is:

ALTER TABLE table_name ADD column_name column_definition;

Example

Let's look at a SQL ALTER TABLE example that adds a column.

For example:

ALTER TABLE supplier ADD supplier_name char(50);

This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.

Add multiple columns in table

Syntax

To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);

Example

Let's look at SQL ALTER TABLE example that adds more than one column.

For example:

ALTER TABLE supplier ADD (supplier_name char(50), city char(45));

This SQL ALTER TABLE example will add two columns, supplier_name as a char(50) field and city as a char(45) field to the supplier table.

Modify column in table

Syntax

To modify a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB:

ALTER TABLE table_name MODIFY column_name column_type;

For SQL Server:

ALTER TABLE table_name ALTER COLUMN column_name column_type;

For PostgreSQL:

ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition;

Example

Let's look at an example of how to modify a column called supplier_name using the ALTER TABLE statement. Note that most databases have a slightly different syntax.

For Oracle:

ALTER TABLE supplier MODIFY supplier_name char(100) NOT NULL;

For MySQL and MariaDB:

ALTER TABLE supplier MODIFY supplier_name VARCHAR(100) NOT NULL;

For SQL Server:

ALTER TABLE supplier ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;

For PostgreSQL:

ALTER TABLE supplier ADD supplier_name char(50);0

Modify multiple columns in table

Syntax

To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:

For Oracle:

ALTER TABLE supplier ADD supplier_name char(50);1

For MySQL and MariaDB:

ALTER TABLE supplier ADD supplier_name char(50);2

For PostgreSQL:

ALTER TABLE supplier ADD supplier_name char(50);3

Example

Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called supplier_name and city.

For Oracle:

ALTER TABLE supplier ADD supplier_name char(50);4

For MySQL and MariaDB:

ALTER TABLE supplier ADD supplier_name char(50);5

For PostgreSQL:

ALTER TABLE supplier ADD supplier_name char(50);6

Drop column in table

Syntax

To drop a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE supplier ADD supplier_name char(50);7

Example

Let's look at an example that drops (ie: deletes) a column from a table.

For example:

ALTER TABLE supplier ADD supplier_name char(50);8

This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.

Rename column in table

Syntax

To rename a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle and PostgreSQL:

ALTER TABLE supplier ADD supplier_name char(50);9

For SQL Server (using the stored procedure called sp_rename):

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);0

For MySQL and MariaDB:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);1

Example

Let's look at an example that renames a column in the supplier table from supplier_name to sname.

For Oracle (9i Rel2 and up) and PostgreSQL:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);2

For SQL Server (using the stored procedure called sp_rename):

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);3

For MySQL and MariaDB:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);4

In MySQL and MariaDB, you must specify the data type of the column when you rename it.

Rename table

Syntax

To rename a table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);5

For SQL Server (using the stored procedure called sp_rename):

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);6

Example

Let's look at an example that renames a table called supplier to the new name vendor.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);7

For SQL Server (using the stored procedure called sp_rename):

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);8

Practice Exercise #1:

Based on the departments table below, rename the departments table to depts.

ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);9

Solution for Practice Exercise #1:

The following SQL ALTER TABLE statement would rename the department_name column to dept_name in the departments table: