What is Unique Constraint in SQL Server

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.

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

Create Unique Constraint

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

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.

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.

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.

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“.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *