What is Unique Constraint in SQL Server - SQL Server / TSQL Tutorial Part 95

What is Unique Constraint in SQL Server:

Unique Constraint in SQL Server is created on a column or columns to restrict the column/s to accept only unique values.
Only single Null value is allowed in a column on which Unique Constraint is created.

Scenario:

Let's say that you are working as SQL Server developer for insurance company, you are asked to create dbo.Customer table that should have a column SSN and it should always accept Unique values.


Solution:

For the above scenario, we can use Unique Constraint on SSN Column. Below script can be used to create unique constraint on a column.

USE [YourDatabaseName]
GO
CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] VARCHAR(11),
    Unique(SSN)
) 

--Insert sample records in table 
Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza','M',Null)
,('Aamir','Shahzad','000-00-0001')
,('Aamir','Shahzad','000-00-0002')



Check the data in dbo.Customer table by using Select query
How to add Unique Constraint on a Column in SQL Server 

Let's use the system views to check if Unique Constraint is added and what name SQL Server has given to it.

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'UNIQUE'

How to add Unique Constraint in SQL Server 

As we can see that the SQL Server has given "UQ__Customer__CA1E8E3C7E8AFCB1" name to Unique Constraint. If we want to implement some naming convention then we should had provided name by ourselves.

If we try to insert the value that already exists in Unique Constraint column, we will get below error.

Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza','M',Null)


Msg 2627, Level 14, State 1, Line 11
Violation of UNIQUE KEY constraint 'UQ__Customer__CA1E8E3C7E8AFCB1'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is ().
The statement has been terminated.

If you would like to create the Unique Constraint with some naming convention, we can use below syntax.

USE [YourDatabaseName]
GO
CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] VARCHAR(11),
    Constraint UQ_Dbo_Customer_SSN Unique(SSN)
) 




Video Demo : What is Unique Constraint in SQL Server





No comments:

Post a Comment