SQL UNIQUE KEY Constraint

SQL UNIQUE KEY Constraint

The UNIQUE KEY is also an important key factor for any table or schema design in SQL. Keeping this in mind the interviewer can ask the different questions from the Unique Key as well. In this article, we will walk through the UNIQUE KEY with examples and related interview questions as well.

I will highly recommend you to please read the previous Primary Key Constraint article before continuing this article here:

SQL Primary Key Constraint and Misconceptions

Please visit our YouTube Channel for SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Introduction

In the database, a key consists of one or more columns of a table that have specific properties. So when you define a table, you specify the key. There are three different keys known as Primary Key, Unique Key, and Foreign Key. They are also known as respective constraints as well. In this article, we will focus on UNIQUE KEY in detail.

SQL Constraints

In SQL, Constraints are the rules that the database enforces to improve data integrity. They are also used to specify the business rules for data in a table.

SQL Constraints are used to restrict the type of data that can be inserted into a table. By doing so, it ensures the accuracy and reliability of the data in the table.

If there is any violation between the defined constraint and the data to be inserted, updated, or deleted, the respective action is aborted or terminated by the SQL.

A user can define constraints either at column level or table level. The column-level constraints apply only to a column, while table-level constraints apply to the whole table.

SQL Constraints can be specified when the table is created by using CREATE TABLE statement, or after the table is created by using ALTER TABLE statement.

SQL UNIQUE KEY Constraint

The main job of the Unique Key is to place additional unique conditions on a column. A Unique Key is also known as a Unique Constraint. The UNIQUE KEY constraint specifies that each Non-NULL value in the constrained column must be unique. 

The Unique Key Constraint ensures that all values in a column are unique. This constraint prevents duplicate values in any column. There can be multiple UNIQUE KEY Constraints in any table and a UNIQUE KEY can also be a composite key.

The UNIQUE Key constraint allows the NULL value by default (only one NULL value). So you can prevent NULL value by combining the NOT NULL Constraint explicitly in the UNIQUE KEY Constraint as well.

Please watch the UNIQUE Key video here for more details. 

UNIQUE KEY Fundamental Points

1. There can be more than one UNIQUE KEY Constraint in any table.  While there can be only one Primary Key in any table. 

2. The UNIQUE KEY Constraint guarantees that no Duplicate Values are inserted. If you try to insert any duplicate value in the Unique Key column you will get the below error:

Msg 2627, Level 14, State 1, Line 10

Violation of UNIQUE KEY constraint 'UK_ID'. Cannot insert duplicate key in object 'dbo.tblEmployee'. The duplicate key value is (0).

The statement has been terminated.

3. A UNIQUE KEY generally creates a Separate Index Data Page, unlike a PRIMARY KEY. Primary Keys are always stored within the index which is created by the key itself. So this primary index does not take up additional disk space, unlike secondary indexes. But the Unique key requires additional disk space as it is not stored within the data page.

If you see the PageType = 1 is nothing but the data page and IndexID = 0 confirms that it is a Heap Table as there is no Clustered Index at all on the table. 

On the other hand for the Unique Key's Secondary Index, there is a separate IAM Page (PageType = 10) and Index Page (IndexID = 2) is created. Here the IndexID = 2 means the Non-Clustered Index and PageType = 2 means it is a secondary or non-clustered Index Data Page.

Please watch the SQL 8KB Page video here for more details about the Index Page, Data Page, and IAM Page.

4. The value of a UNIQUE KEY column can never be modified or deleted if any FOREIGN KEY refers to that UNIQUE KEY. If you try to UPDATE a Unique Key record that is referenced by the Foreign Key, you will get the below error:

Msg 547, Level 16, State 0, Line 12

The UPDATE statement conflicted with the REFERENCE constraint "FK_ID". The conflict occurred in database "TechPoint", table "dbo.tblEmployeeRole", column 'EmpID'.

The statement has been terminated.

Similarly, if you try to DELETE a Unique Key record that is referenced by the Foreign Key, you will get the below error:

Msg 547, Level 16, State 0, Line 13

The DELETE statement conflicted with the REFERENCE constraint "FK_ID". The conflict occurred in database "TechPoint", table "dbo.tblEmployeeRole", column 'EmpID'.

The statement has been terminated.

Please watch the FOREIGN KEY video here for more details.

5. All the Relational Database Management Systems support Unique Keys.  In fact, No-SQL Database like COSMO also has the concept of a Unique Key. 

6. When multiple columns are used as a Unique Key, it is known as a Composite Unique Key. In the case of designing the Composite Unique Key, you should use as few columns as possible because the more columns you use for the Unique Key the more storage space you require in the disk which may cause a performance overhead.

CREATE TABLE  CustomerOrder

(

CustomerID INT NOT NULL,

ProductID INT NOT NULL,

OrderQuantity INT NOT NULL,

UNIQUE NONCLUSTERED (CustomerID, ProductID)

);

In the above SQL CREATE TABLE statement, there is a Composite UNIQUE Key on CostomerID and ProductID columns.

7. The Unique Key can be added while designing the table by CREATE TABLE statement. 

CREATE TABLE tblEmployee

( ID  INT NOT NULL CONSTRAINT UK_ID UNIQUE CLUSTERED, 

Name VARCHAR(50)   

);

If the table is already created, in that case, a Unique Key can be added by the ALTER TABLE statement.

ALTER TABLE tblEmployee ADD CONSTRAINT UK_ID UNIQUE CLUSTERED(ID);

8. When you use ALTER TABLE statement to add a Unique Key on a column that contains data already, the Unique Key column must not contain any Duplicate Record. If there is any, you will get the below error:

Msg 1505, Level 16, State 1, Line 9

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tblEmployee' and the index name 'UK_ID'. The duplicate key value is (1).

Msg 1750, Level 16, State 0, Line 9

Could not create constraint or index. See previous errors.

The statement has been terminated.

If you closely see the error message, it fails to create the implicit UNIQUE INDEX which is required by the Unique Key Constraint.

Please watch the UNIQUE INDEX video here for more details.

9. A Temp Table can also have a Unique Key either while creating the Temp Table by CREATE TABLE command or by ALTER TABLE statement. This is one of the most common confusing Interview Questions of SQL.

Please watch the SQL Temp Table video here for more details.

10. A Table Variable can also define a Unique Key but only at the time of declaration. You cannot add a Unique Key on a Table Variable later by ALTER command. This is also one of the most common confusing Interview Questions of SQL.

Please watch the SQL Table Variable video here for more details.

11. A Table-Valued Type (TVP) can also have a Unique Key but it must be specified only at the time of type declaration. You cannot add a Unique Key on a Table-Valued Type later by ALTER command. In fact, you cannot modify any SQL User Defined Type, you always have to drop and recreate again. This is also one of the most common confusing Questions of SQL Interviews.

Please watch the SQL Table Valued Type video here for more details.

12. A UNIQUE KEY also cannot be created on any MAX Size data type for example VARCHAR(MAX), NVARCHAR(MAX). If you try to do so you will get the below error:

Msg 1919, Level 16, State 1, Line 2

Column 'Email' in table 'tblEmployee' is of a type that is invalid for use as a key column in an index.

Msg 1750, Level 16, State 0, Line 2

Could not create constraint or index. See previous errors.

The reason behind the above error is that the column (Email) is invalid for creating an index because an index cannot be created on a datatype column which cannot be stored on an 8KB page. Basically, all the MAX Size data types are stored within a LOB, they are not stored in an 8KB data page.

Please watch the SQL 8KB Page video here and How the data is stored physically on the 8KB Page here for more details.

13. A UNIQUE KEY can be created on both Nullable and Non-Nullable Column, either by CREATE TABLE Command or by ALTER TABLE Command. But if it is a nullable column, only one NULL value will be allowed at all.

Please watch the SQL NULL Value video here for more details.

14. You cannot DELETE or DROP the Index created by the Unique Key explicitly. It will only be deleted when you DROP the Unique Key Constraint. If you try to delete the Index by the DROP INDEX command, you will get the below error:

Msg 3723, Level 16, State 5, Line 7

An explicit DROP INDEX is not allowed on index 'tblEmployee.UK_ID'. It is being used for UNIQUE KEY constraint enforcement.

15. A UNIQUE KEY cannot be added on View, like the Indexes. But you can add Index on a view without any issue. This is the reason why this question is asked in the interview most of the time due to confusion between Index and Key on view.

Please watch the Clustered Index video here and the Non-Clustered Index video here for more details.

16. You can check about the UNIQUE KEY and UNIQUE INDEX details by below SQL Query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='tblEmployee';

EXEC sp_helpindex 'tblEmployee';

17. UNIQUE KEY cannot be added in any View but a UNIQUE INDEX can be added on View. This is the most important difference between UNIQUE KEY and UNIQUE INDEX.  This is the reason why both Unique Key and Unique Index are there in SQL.

18. Maximum 999+1 Unique Key can be added to any table or view. If you try to add more than that an error will be generated: (Tested on SQL Server 2014 SP1 - 12.0.2000.8)

Msg 1910, Level 16, State 1, Line 7

Could not create nonclustered index 'Uniqu_t1_1001' because it exceeds the maximum of 999 allowed per table or view.

Msg 1750, Level 16, State 0, Line 7

Could not create constraint or index. See previous errors.

Actually, 999 is the restriction for the number of non-clustered indexes on a table, not the unique constraints limit. Since Unique Key uses the index internally, it is also not allowed more than 999.

So there is one UNIQUE KEY place always reserved for the Primary Key and we know that it uses clustered index by default. Therefore we can add one more unique key with clustered index explicitly. Thus max 1000 Unique Keys are allowed in any table. You can check all the Unique Key lists by below SQL Query:

EXEC sys.sp_helpconstraint 'table-name';

19. If  999 Non-Clustered Indexes are already created explicitly on the table by CREATE INDEX command, no UNIQUE KEY  can be created at all on the table. However, if the Primary Key is not created already, you can add one UNIQUE Key on that Clustered Index forcefully.  But after doing that no Primary Key can be created on the table at all.

Common Misconceptions About UNIQUE KEY

There are some misconceptions about the UNIQUE KEY among the developers and the interviewee. Here we will see some of them.

Misconception 1: UNIQUE KEY NEVER ALLOWS NULL VALUES

One misconception about the Unique Key is that a NULL value cannot be inserted into a UNIQUE KEY column. But it is not true.

The UNIQUE KEY by default allows NULL values. But one and only one NULL value can be inserted into the UNIQUE KEY column if it is created on a nullable column.

Please watch the SQL 3VL Logic video here to know why only one NULL value is allowed by the UNIQUE KEY Constraint.

If the UNIQUE KEY is a Composite Key, multiple NULL values are also allowed for all the best possible UNIQUE Combinations with NULL values.

Please watch the SQL NULL Value video here for more details about the SQL NULL value.

Misconception 2: BLANK (EMPTY) VALUE or WHITESPACE ARE NOT ALLOWED IN UNIQUE KEY

One misconception about the Unique Key is that a Blank Value cannot be inserted into the UNIQUE KEY. But it is not true anymore. 

A blank or whitespace value is always allowed in the Unique Key column, but only one blank value is allowed. 

If the data type of the Unique Key column is numeric, by default 0 will be inserted in the place of blank or empty string. On the other hand, if the Unique Key column is a string type, the white space or blank string will be inserted.

But as soon as you will try to insert the second blank or an empty string in the Unique Key column, you will get a constraint violation error.

Msg 2627, Level 14, State 1, Line 10

Violation of UNIQUE KEY constraint 'UK_ID'. Cannot insert duplicate key in object 'dbo.tblEmployee'. The duplicate key value is (0).

The statement has been terminated.

Misconception 3: UNIQUE KEY NEVER ALLOWS DUPLICATE VALUES

One common misconception about Unique Key is that a UNIQUE KEY never allows Duplicate Values in the Unique Key columns. But it is not absolutely true actually. It is only true in the case of the column-level constraint key.

So if the Unique Key is a single column, it never allows any Duplicate Value. But if the UNIQUE KEY is a Composite Unique Key, each individual column may contain Duplicate Values; however, the combination of composite column values will always be unique.

You can see that value 1 and 2 is duplicate in both CustomerID and ProductID columns but their combination is always unique. If you try to insert the below record you will get an error:

INSERT INTO CustomerOrder VALUES(1, 2, 50);

Misconception 4: UNIQUE KEY ALWAYS CREATE NON-CLUSTERED INDEX

Another famous misconception about the Unique Key is that the UNIQUE KEY always creates a NON-CLUSTERED INDEX. But actually, it is not true.

The UNIQUE KEY Constraint by default creates the UNIQUE NON-CLUSTERED INDEX internally. But you can create a CLUSTERED INDEX as well on UNIQUE KEY if there is none defined already.

Please watch the CLUSTERED INDEX vs NON-CLUSTERED INDEX video here for more detail.

Misconception 5: UNIQUE KEY SHOULD ALWAYS BE A NON-CLUSTERED INDEX

This is also a misconception that a UNIQUE  KEY should always be a NON-CLUSTERED INDEX. But it is not necessary. It is not mandatory that a Unique Key should always be a Non-Clustered Index.

We generally keep the Unique Key on the Non-Clustered Index because the Clustered Index is already occupied by the Primary Key. If you do not specify it explicitly, SQL will create the non-clustered index implicitly.

Please watch the Non-Clustered Index video here for more details about how the secondary index is working in SQL.

If in your case you think that the major filtering is based on the Unique Key you can make that column as Clustered Index instead of Primary Key column.

Misconception 6: NO EXPLICIT INDEX CAN BE CREATED ON UNIQUE KEY COLUMN

One more common misconception about the Unique Key is that it cannot contain any explicit index. But it is not true.

A column can contain multiple indexes in SQL. So the Unique Key column also contains multiple indexes i.e you can create a custom explicit index on the Unique Key column as well.

If you have already a Unique Index on a column (ID), it is interesting that SQL Server did not recognize that there is already a covering Index and use that one. If you try to add Unique Key on that column (ID) the SQL will create a new Non-Clustered Index for the Unique Key instead.

Please watch the Clustered Index vs Non-Clustered Index video here for more details.

Misconception 7: UNIQUE KEY CANNOT TURN A TABLE FROM HEAP TO NON-HEAP (CLUSTERED TABLE)

This is the most common misconception that only a PRIMARY KEY can convert a HEAP table into a NON-HEAP (Clustered) table. But it is not true actually. A Unique Key can also do the same thing also. 

The PRIMARY KEY does not turn a HEAP Table into CLUSTERED Table always. If the PRIMARY KEY is created on a NON-CLUSTERED INDEX the table will still remain as a HEAP Table. 

On the other hand, if a table does not contain any PRIMARY KEY but contains any CLUSTERED INDEX, the table will be a NON-HEAP i.e CLUSTERED Table instead of HEAP.

Actually, when a CLUSTERED INDEX is created either explicitly by CREATE INDEX command or by the PRIMARY KEY, SQL Engine deletes all the existing data pages and creates a new one based on the Clustered Column.

Please watch the PRIMARY KEY vs CLUSTERED INDEX video here for more details.

Misconception 8: UNIQUE KEY AND NON-CLUSTERED INDEX ARE THE SAME

One more famous misconception is that the Unique Key and Non-Clustered Index are the same in SQL. But it is never true. Both are two different things.

The reason behind this misconception is that a UNIQUE KEY creates a NON-CLUSTERED INDEX by default. Since the Clustered Index is created by the Primary Key itself already, now no one can create any other Clustered Index explicitly on the table anymore.

But if there is any CLUSTERED INDEX defined already in the table, UNIQUE  KEY always creates a NON-CLUSTERED INDEX forcefully.

Please watch the CLUSTERED INDEX video here, and the NON-CLUSTERED INDEX video here for more details.

Misconception 9: UNIQUE KEY AND UNIQUE INDEX ARE THE SAME

It is a very common and famous misconception that the Unique Key and Unique Index are the same in SQL. But it is not true. Both are two different things.

The reason behind this misconception is that a UNIQUE KEY creates a UNIQUE INDEX by default. 

But the UNIQUE INDEX created by the UNIQUE KEY cannot be deleted forcefully. You cannot add UNIQUE KEY on a view but UNIQUE INDEX can be created on View.

Please watch the CLUSTERED INDEX video here, and the NON-CLUSTERED INDEX video here for more details.

Misconception 10: UNIQUE KEY CANNOT BE AN IDENTITY COLUMN

One more general misconception is that the Unique Key Column cannot be Identity Column. But it is not true. Both are two different things. There is no dependency on each other.

Unique Key and IdentityColumn both are two different things. We can also make a Unique Key as an Identity Column. We generally use the Primary Key as Identity Column, so that it always creates a new unique key value for the Primary Key. 

Misconception 11: UNIQUE KEY COLUMN CANNOT BE REFERENCED BY FOREIGN KEY CONSTRAINT

One of the most common misconceptions is that the Unique Key Column cannot be referenced by the Foreign Key Constraint. But it is not true. 

A Unique Key column can also be referenced by the FOREIGN KEY CONSTRAINT same as the Primary Key column. In fact, any column having UNIQUE INDEX can be referenced by the Foreign Key.

But in the case of the UNIQUE KEY Column, you have to specify the unique key column name explicitly in the Foreign Key declaration. On the other hand, if you did not specify the column name the primary key of the referenced table is by default referenced

Please watch the Foreign Key video here for more details.

Conclusion

Unique Key is a constraint that is used to define the uniqueness of every value of a column or set of columns of the table. 

Unique Key can be created on both Clustered and Non-Clustered Index. But it can be created on the nullable column as well.

Unique Key can also be referenced by the Foreign Key Constraint same as Primary Key.

Live Demo

Recommended Articles

Most Frequently Asked SQL Interview Questions
Most Frequently Asked C# Interview Questions
Foreign Key Interview Questions
Unique Key Interview Questions
Primary Key Interview Questions
Derived Table (DT) Interview Questions
Unicode vs Non-Unicode Data Types Interview Questions
Table Variable (TV) Interview Questions
Table-Valued Type (TVT) Interview Questions
Common Table Expression(CTE) Interview Questions
Temp Table Interview Questions
Static Class Interview Questions and Answers
Sealed Class Interview Questions and Answers
Sealed Method Interview Questions and Answers
Abstract Class vs Interface
Interface Interview Questions and Answers

Thanks for visiting this page. Please follow us on Twitter, Facebook, LinkedIn, Telegram, WhatsApp, Youtube, and Quora for regular updates.