Unique Key

In this blog, we’ll discuss about Unique Key.

  1. Unique key is another of the most commonly in use constraint in MS SQL.
  2. Multiple Unique key can be defined in a table.
  3. It can be reference by Foreign key to set the relation between tables.
  4. Unique key guarantees the value of the column to be Unique only.
  5. It’ll allow user to insert only 1 NULL value in the Unique column, if the column is set to accept NULL value.
  6. It can be defined in column with NULL or NOT NULL.
  7. It can be defined on a single column or on multiple columns or composite columns in a table.
  8. By default, Unique key creates a Unique Non Clustered Index in the table.
  9. At any time, if we disable the Unique Non Clustered default index created with the Unique Key, we can still access the table.
  10. However the property of the Unique key is not going to work now ( from above point 6) though we have the Unique present in the table. It means if you try to insert duplicate values or NULL value in the unique key column it is possible.
  11. Unique key never sort the data. It is the property of index. If we create the Clustered Index it’ll do the sort otherwise by default it’ll not with non clustered index.
  12. Unique key can also be created with Unique Clustered Index.
  13. On deleting the Unique Key manually, it’ll automatically drop the index associated with it(Clustered/Non-Clustered) and on deleting the Index associated with Unique key it’ll automatically drop the Unique key.
  14. We can define, Unique key and Non Clustered Index on different columns only if we define the Unique Key with Clustered Index.
  15. Anybody can define Unique key in existing table with data. It can be achieved by remove the duplicate values and remove multiple NULL values as Unique key accepts one NULL value.
  16. Any need to change the column’s datatype or need to drop the column (Column which is having the Unique key) entirely will require first DROP of the UNIQUE constraint on the column prior to your action.

Examples

–Example1: Default Unique Key, which will create a Unique Non Clustered Index on ID column
create table test
(
        Id int unique,
        name varchar(50) not null
)
–Example2: Default Unique Key, which will create a Unique Non Clustered Index on NOT NULL ID column
create table test
(
        Id int not null unique,
        name varchar(50) not null
)
— Example3: Default Unique Key, which will create a Unique Clustered Index on ID column
create table test
(
         Id int not null unique clustered,
         name varchar(50) not null
)
— Example4: Composite Unique Key on multiple columns
create table test
(
     Id int,
     name varchar(50) not null,
     unique (Id,name)
)
–Example5:  Multiple Unique Key on multiple columns
create table test
(
          Id int,
          name varchar(50) not null,
          unique (Id),
          unique (name)
)
–Example6:  Unique Key creation with constraint keyword. It’ll create a Unique key with name UK_ID and index with name UK_ID
create table test
(
       Id int,
       name varchar(50) not null,
       constraint UK_ID unique(ID)
)
–Example7:  Unique Key creation outside table definition. It’ll create a Unique key with name UK_ID and index with name UK_ID
create table test
(
       Id int,
       name varchar(50) not null
)
GO
— It creates a unique non clustered index
Alter table test add constraint [UN_ID] unique(ID)
— It creates a unique clustered index
Alter table test add constraint [UN_ID] unique clustered(ID)

To check or verify the index created on the table, we can use the below command

SELECT *
FROM sys.indexes I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON S.schema_id = T.schema_id
WHERE
T.Name = ‘test’                             — Table name
AND S.Name = ‘dbo’                    — Schema name/optional

To get detail information about all the Unique keys created in all the tables in the current database.

SELECT
@@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS TableName,   
c.name ColumnName,
t1.name as ColDataType,
c.max_length as ColMaxLength,
c.precision as ColPrecision,
c.scale as ColScale,
(case c.is_identity when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_IdentityType,
(case c.is_nullable when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_NullableType,
kc.name ConstraintName,
(Case i.is_disabled When 0 Then ‘No’ When 1 Then ‘Yes’ End) As UK_Index_IsDisable,
(case i.is_unique when 1 Then ‘Yes’ When 0 Then ‘No’ End) AS UK_Index_IS_Unique,
(Case i.index_id When 0 Then ‘Heap’ When 1 Then ‘Clustered’ Else ‘NonClustered’ End) As UK_IndexType,
(Case i.type
When 0 Then ‘Heap’
When 1 Then ‘Clustered’
When 2 Then ‘Nonclustered’
When 3 Then ‘XML’
When 4 Then ‘Spatial’
When 5 Then ‘Clustered columnstore’             –Applies to: SQL Server 2014 & Above
When 6 Then ‘Nonclustered columnstore’      –Applies to: SQL Server 2012 & Above
When 7 Then ‘Nonclustered hash’                    –Applies to: SQL Server 2014 & Above
End) AS UK_IndexSubType,
(case ic.is_descending_key when 0 Then ‘ASC’ When 1 Then ‘DSC’ End) AS UK_Index_SortOrder,
(case ic.is_included_column when 0 Then ‘No’ When 1 Then ‘Yes’ End) AS Col_IsIncluded
from
sys.key_constraints kc
inner join sys.objects o
on o.object_id = kc.parent_object_id
inner join sys.indexes i        
on i.name = kc.name
AND i.is_primary_key<>1               — get only the indexes associated with unique key
inner join sys.index_columns ic
on ic.object_id = kc.parent_object_id and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = kc.parent_object_id and c.column_id = ic.column_id
INNER JOIN sys.types t1                   — Get the column datatypes
ON c.user_type_id = t1.user_type_id
Where
kc.type=’UQ’                                      — For Unique key=UQ
order by 3,4

To drop or remove the Unique constraint , we need to provide the constraint name. If the constraint name is auto generated then get it GUI from the left side in SSMS under the table => Goto constraint => click on the constraint and drag it over the command window.

ALTER TABLE [dbo].[test] DROP CONSTRAINT [UN_ID]

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

One thought on “Unique Key

  1. Pingback: Integrity Constraint in SQL | Lessons I Learned from sql

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s