UNIQUE constraints are enforcing no duplicates can exists for a column or combination of columns for a table. We can define constraints in 3 ways:

1. while creating a table
2. using ALTER TABLE statement
3. using SQL Server Management Studio

Let us quickly see with a simple example as below.

Drop table if exists TestTable
Create Table TestTable ( ID int UNIQUE, FirstName varchar(50) )

sp_help 'TestTable'

sp_help system procedure can be used to understand the table properties. If we observe the result of sp_help, we can see two important information.

1. constraint name – unique constraint created.
2. index name – though we have not created any index separately, there is one created as the same name of constraint.

So, when we create a UNIQUE constraint, SQL Server automatically creates a nonclustered index on the table. And this index is responsible for enforcing the uniqueness on the column(s).

Can we drop the index created?

No, we will never be able to drop those indexes until we drop the constraints.

Can we insert NULL values to a UNIQUE column?

Yes, we can have ONLY one NULL value to a UNIQUE column. If we try to insert again, it will fail for the same reason of duplicate violation.

Can we define UNIQUE for more than one column?

Yes, we can define on combination of columns. Interestingly, by defining on composite, we can have NULL values for each individual columns once and one for combination as well.

Drop table if exists TestTable
Create Table TestTable ( ID int , FirstName varchar(50) , UNIQUE(ID,FirstName))

--Data inserts
Print 'First insert'
Insert into TestTable Values(1,NULL)
Print 'Second insert'
Insert into TestTable Values(NULL,NULL)
Print 'Third insert'
Insert into TestTable Values(NULL,'SQL')
Print 'Fourth insert'
Insert into TestTable Values(100,'some name')

If you enjoyed this blog post, feel free to share it with your friends!