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



3 comments:

Anonymous said...

hi how if i have an existing table and i want to add new column and set it as unique?

Shamas DBA said...

Hi, You can create column using Alter table command and add unique key by setting its option, as mentioned query below

ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE ;

If you have data in table then you need to do this in 4 steps.
1- Create new column with NULL
2- Update data in column
3- Alter column as not null
4- Add unique key constraint.

Shamas DBA said...

Step 4 command is as follows

USE AdventureWorks2012;
GO
ALTER TABLE Person.Password
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);
GO