Adding Constraints to Existing Tables

Constraints can also be added to tables that have already been created. To add a constraint to an existing table you must use the ALTER TABLE statement. This statement is used to add or delete columns and constraints in an existing table. Following is the basic syntax to alter an existing table:

    ALTER TABLE Tablename
    ADD COLUMN ColumnName ColumnType (Size) ColumnConstraint |
    DROP COLUMN ColumnName |
    ADD CONSTRAINT ColumnConstraint |
    DROP CONSTRAINT ColumnConstraint;

In this tutorial, the ALTER TABLE statement is used to add and delete constraints to existing tables. Take a look at Example-D, which shows how to add the UNIQUE constraint to an existing table.

Example-D

You want to add a UNIQUE constraint to the ToyName column in the Toys table. Look at the following script:

ALTER TABLE Toys
ADD CONSTRAINT ToyNameUnique UNIQUE (ToyName);

This SQL script uses the ALTER TABLE statement to add the UNIQUE constraint to the ToyName column in the Toys table. The ALTER TABLE keywords are used to specify the table name (Toys). The ADD CONSTRAINT keywords are used to specify the constraint name (ToyNameUnique), the type of constraint (UNIQUE), and the name of the column (ToyName) to add the constraint to.

To delete the UNIQUE constraint from the ToyName column in the Toys table, simply type the following:

ALTER TABLE Toys
DROP CONSTRAINT ToyNameUnique;

In the script, the ALTER TABLE keywords are used to specify the table name (Toys), and the DROP CONSTRAINT keywords are used to specify the name of the constraint to delete (ToyNameUnique).