We can create Key constraints on a table and there are 2 types on Unique constraints.
Primary Key and Unique Key
following is the comparison and usage of unique key constraints
- UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness
- UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
- Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.
- Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL but only NULL will allow.
- A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.
- Unique Key constraint creates Non-clustered Index when there is Primary Key exists in a table.
We can create Unique key constraint using T-SQL Command and SSMS
T-SQL:
To create a UNIQUE constraint when you create a table
Create Table dbo.Doc_exec
(ID int primary key,
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
)
To create a UNIQUE constraint on an existing table
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE ;
To delete a UNIQUE constraint
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
SSMS:
right click on a table in design mode
Click on Columns and you can select multiple columns in a unique key
Change Unique value to True
