What is Unique Constraint in SQL Server

dbtut

6 years ago

What is Unique Constraint in SQL Server?

The Unique Constraint guarantees the uniqueness of the colum or column on the table. It can be created on one or more columns in the table.

For example, suppose that you have a table with an ID column and a CountryName column.

If you create a Unique Constraint on the ID column, you ensure that the ID value will be unique.

So, the same ID can not inserted to the table.

First, create a table with the help of the following script.

CREATE TABLE [dbo].[Country](
[ID] [int] NULL,
[CountryName] [varchar](250) NULL)
ON [PRIMARY]

CREATE TABLE [dbo].[Country](

[ID] [int] NULL,

[CountryName] [varchar](250) NULL)

ON [PRIMARY]

I will also add a record to the table with the following script.

INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (1,'England')

1	INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (1,'England')

Create Unique Constraint

To create our Unique Constraint, we use the following script.

USE [Test] 
GO 
ALTER TABLE [dbo].[Country] ADD CONSTRAINT ConstraintName UNIQUE(ID);  
GO

USE [Test]

ALTER TABLE [dbo].[Country] ADD CONSTRAINT ConstraintName UNIQUE(ID);

Now, the same value can not be inserted more than once for the ID column. The ID value is 1 in our previous record.

Let’s try to make an insert using the same ID value as below. It will give an error as below.

INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (1,'Turkey')

1	INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (1,'Turkey')

Violation of UNIQUE KEY constraint ‘ConstraintName’. Cannot insert duplicate key in object ‘dbo.Country’. The duplicate key value is (1).

Does unique constraint allow null values in SQL Server?

Yes, we can insert one null value if there is unique constraint on the column. If we try to insert second null value to the column which has unique constraint, we receive the below error.

INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (null,'Turkey')
INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (null,'Germany')

1 2	INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (null,'Turkey') INSERT INTO [dbo].[Country]([ID],[CountryName]) VALUES (null,'Germany')

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint ‘ConstraintName’.

Cannot insert duplicate key in object ‘dbo.Country’. The duplicate key value is (<NULL>).

The statement has been terminated.

DROP Unique Constraint

You can drop unique constraint as follows.

USE [Test]
GO
ALTER TABLE [dbo].[Country] DROP CONSTRAINT [ConstraintName]
GO

USE [Test]

ALTER TABLE [dbo].[Country] DROP CONSTRAINT [ConstraintName]

If you want to learn more about Primary Key and Foreign Key and relationship between them, you may want to read the article “What is Primary Key and Foreign Key“,

You can learn the differences between Primary Key and Unique Constraint by reading the article “Differences Between Primary Key and Unique Constaint“.