Search This Blog & Web

Thursday, May 3, 2012

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



Post a Comment