Constraints In Microsoft SQL Server 2000 - Unique Constraints
(Page 6 of 8 )
The easiest to handle, Unique constraints are essentially the younger siblings to Primary keys. They require unique value throughout the named column or combination of columns in the table. Often, Unique constraints are referred to as Alternate Keys. Alternate keys are not considered to be the unique identifier of a record in a table. Unique constraints can be multiple. Once we establish a Unique constraint, every value in the named column must be unique. SQL Server will show an error if you try to update or insert a row with a value that already exists in a column with a Unique constraint.
Unlike Primary key, a Unique key will not automatically prevent us from entering NULL values, we have to explicitly state the null value of the column. Keep in mind though, if we do allow NULL values, we will still be able to insert only one of them. Although a NULL does not equal another NULL, it is a duplicate value to the Unique constraint.
Let us create another table in our Accounting database called Shipment. Example 9:
USE Accounting
CREATE TABLE Shipment
(
ShipperID int IDENTITY NOT NULL
PRIMARY KEY,
ShipperName varchar(30) NOT
NULL,
Address varchar(25) NOT NULL,
City varchar(25) NOT NULL,
State
varchar(2) NOT NULL,
Zip
varchar(10) NOT NULL,
Phone
varchar(14) NOT NULL
UNIQUE
)
Creating A Unique Constraint on an Existing Table is simple and similar to adding a Primary key. Look at Example 10:
Use Accounting
ALTER TABLE Employees
ADD CONSTRAINT
Ak_EmpSSN
UNIQUE
(SSN)
Next: Check Constraints >>
More Database Code Articles
More By Gayathri Gokul