Description

The ALTER TABLE statement can be used to alter or modify an existing table.

  • It can be used to add, modify, or delete table columns.
  • It can be used to add and drop table constraints.
  • It can be used to rename a table.

After creating a table, we may get into a situation where we need to modify the table definition of a table that is in use. In such situations, the ALTER TABLE statement comes handy to do such changes on an existing table without corrupting the table data.

Let's look at an example of the ALTER TABLE statement to add a constraint.

Table Data

Before executing the query, consider the below table data.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Example

The below SQL ALTER TABLE statement adds a UNIQUE constraint on the column customer_name of the table.

Run this on IDE

ALTER TABLE customers 
ADD UNIQUE (customer_name);

After successful execution, the table data looks as below.

  • From here on the table will not allow duplicate values in the customers_name column.
customer_id customer_name email address city country postal_code
1 Maria Anders NULL Obere Str. 57 Berlin Germany 12209
2 Yoshi Nagase NULL 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier NULL 25, rue Lauriston Paris France 75016
4 Martin Blank NULL Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy NULL 89 Chiaroscuro Rd. Portland USA 97219

Overall

We now know how to use ALTER TABLE statement to add a constraint.

Related Links