SQL Server: Unique Key Constraints
The Unique Constraint ensures the uniqueness of all values and no duplicate values are entered in a column of a table.
The unique constraints can be created using CREATE or ALTER TABLE T-SQL. Add unique constraints after columns definitions in the CREATE TABLE statement.
CONSTRAINT <constraint_name> UNIQE(<column_name>)
The following CREATE TABLE statement defines a unique key constraint on the
PhoneNo column of the
CREATE TABLE Employee(EmployeeID int, FirstName nvarchar(50) NOT NULL, LastName nvarchar(50) NOT NULL, EMail nvarchar(50), PhoneNo varchar(15), ADD CONSTRAINT UNQ_Emp_Phone Unique(PhoneNo))
The following ALTER TABLE statement adds a unique constraint on the
PhoneNo column of the existing
ALTER TABLE Employee ADD CONSTRAINT UNQ_Emp_Phone Unique(PhoneNo)
Now, if you try to insert or update a record in the
Employee table where
Phone is not within the specified range, then it will raise the following error.
Violation of UNIQUE KEY constraint 'UNQ_Emp_Phone'. Cannot insert duplicate key in object 'dbo.Employee'. The duplicate key value is (123.123.1834). The statement has been terminated.
- Both Unique constraint and Primary key constraint enforce uniqueness. It is recommended to use Unique constraint instead of Primary key constraint whenever you want to enforce uniqueness in a column.
- Unlike Primary key constraint, Unique constraints allow only one NULL value.
- A unique index is automatically created when a unique key constraint is created.
- SQL Server raises an error whenever a duplicate value is inserted or updated.
- When a Unique index is added to an existing column in a table, the database engine first checks for the uniqueness of the specified column data. If a duplicate entry is found, then the engine returns an error and does not add the constraint.
- A Unique key in a table can be referenced by a Foreign Key from another table.
Create Unique Constraint Using SSMS
Step 1: Open SSMS, login to a database.
In the Object Explorer, expand the table folder and right-click on a table where you want to add a unique constraint and select
Design to open it in a table designer.
Now, right-click on the table designer and select
Indexes/Keys,as shown below.
Step 2: In the "Indexes/Keys" dialog box, select
PhoneNo column in the
Unique Key type to apply the unique key constraint on the
Step 3: give the name of a constraint in the
Click Close and Save the table.
Thus, you can create an unique key constraint on a column in a table.
Delete Unique Key Constraint
Use the DROP CONSTRAINT statement in the ALTER TABLE statement to delete existing unique constraints.
DROP CONSTRAINT <constraint_name>;
The following deletes a unique key constraint
UNQ_Emp_Phone in the
ALTER TABLE Employee DROP CONSTRAINT UNQ_Emp_Phone;
To delete constraints using SQL Server Management Studio, expand the table having the constraint to be deleted.
Keys folder and right-click on a constraint to be deleted and click Delete.
This will open "Delete Object" dialog box, as shown below.
In the "Delete Object" dialog box, click Ok to delete a constraint.