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.
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).
In this tutorial:
- Creating Tables and Inserting Records
- Data Definition Language Component
- CREATE TABLE Syntax
- SQL Data Types
- NULL/NOT NULL Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
- Adding Constraints to Existing Tables
- Constraint Syntax
- Inserting Records
- Inserting Data without Specifying Column Names