Unique Key constraint, Create, Alter and Drop using TSQL and SSMS

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