SQL Interview Questions And Answers - UNIQUE KEY

SQL Interview Questions And Answers - UNIQUE KEY

In SQL Unique Key is the most important topic for both development and interview point of view as well. In this article, we will see some common interview questions which is based on the Unique Key. For experienced developers, there can be a lot of questions based on this topic. The interviewer may try to confuse you by asking the same question in a more tricky way.

I will highly recommend you to please read the previous Primary Key Interview Questions and Answer article before continuing this article here:

SQL Primary Key Interview Questions and Answers

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

Watch SQL Interview Questions and Answers videos here.

Introduction

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 the specified 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.

I will recommend to please read the previous Unique Key Constraint article here first for more details about the Unique Key.

Below are some Common Interview Questions based on the SQL Unique Key Constraint:

Q01. What is the difference between INDEX vs KEY?

In SQL an index is a set of ordered references to the rows of a table. It can contain data from one or more columns of a table based on the index coverage. The index itself is stored on pages, making up the Index Pages in SQL Server.

An index is basically used for improving the performance of data retrieval by reducing the number of physical pages that the database must access in order to read a row in the database. 

Since the indexes always store the data in sorted order, they also eliminate the need to create temporary storage for the ORDER BY clause if the required column is included in the index. 

There are different type of indexes available in SQL, but actually, there is only two categories of any Index: Clustered Index and Non-Clustered Index. 

All other types of Index are derived from these two categories like Unique Index, Spatial Index, Columnstore Index, XML Index, Filtered Index, Hash Index, etc.

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

On the other hand, KEYs are nothing but constraints, and Constraints are the rules that the database enforces to improve data integrity. A Key can also consist of one or more columns same as the Index. But it is also true that both Primary Key and Unique Key use the index internally.

There are five constraints (NOT NULL Constraints, Primary Key Constraints, Unique Key Constraints, Foreign Key Constraints, Check Constraints, Default) out of which three are also known as SQL Keys (Primary Key, Unique Key, Foreign Key).

In SQL the KEY and INDEX are two different things, they are not the same. Though some KEYs like PRIMARY KEY and UNIQUE KEY also create indexes internally when they are created. The main use of an INDEX is performance while the main purpose of KEY is to enforce data integrity.

Please watch the Primary Key video here, Unikey Key video here, and Foreign Key video here for more details.

Q02. What is the difference between PRIMARY KEY vs UNIQUE KEY?

A PRIMARY KEY is a value that is used to uniquely identify the record or individual row of a table, while a UNIQUE KEY is used to enforce uniqueness in a column or set of columns.

Let's see the similarities between PRIMARY KEY and UNIQUE KEY first:

1. Both create UNIQUE Index internally and both can be created on both Clustered Index and Non-Clustered Index.
2. Both can be used to uniquely identify the record.
3. Both can be referenced by a Foreign Key Constraint.
4. Both can be Identity Columns.
5. Both cannot be created on a column having MAX Size data type i.e VARCHAR(MAX) or NVARCHAR(MAX) data type.

Please watch the Primary Key video here for more details.

Now let's see the difference between PRIMARY KEY and UNIQUE KEY:

1. Only one PRIMARY KEY is allowed in any table but multiple UNIQUE KEYs are allowed in any table.
2. The PRIMARY KEY does not allow any NULL Value while the UNIQUE KEY allows a single NULL Value.
3. PRIMARY KEY cannot be created on a NULL-able column while a UNIQUE KEY can be created on the nullable column.
4. PRIMARY KEY creates Clustered Index by default but a UNIQUE KEY creates a Non-Clustered Index by default. But it is not mandatory at all.
5. Generally a PRIMARY KEY does not create a separate index to store the key values while a UNIQUE KEY creates a separate secondary index to store the key values.

Please watch the UNIQUE Key video here for more details.

Q03. What is the difference between UNIQUE KEY vs Non-Nullable UNIQUE KEY?

We know that by default a Unique Key is nullable i.e NULL value can be inserted into a Unique Key Column.  But in the case of Primary Key, it is by default non-nullable, so NULL values are not allowed into it.

However, only one NULL value is allowed if the UNIQUE KEY is nullable. If we want to prevent this single NULL value also from inserting into the column, we can make that column as Non-Nullable by adding NOT NULL Constraint along with UNIQUE KEY Constraint which makes it a Non-Nullable Unique Key.

Please watch the SQL NULL Value video here and the SQL Three Valued Logic video here for more details about NULL values in SQL.  

Q04. What is the difference between the UNIQUE KEY and UNIQUE Index?

Actually, both terms are looking similar but they are two separate things. The Unique Key is not the same as Unique Index.

Both Unique Key and Unique Index are used to enforce the uniqueness of column values. Unique Key create and use a Unique Index internally but that index is being referenced tightly with the unique constraint. 

No one can delete that index explicitly either by DROP command or by ALTER command. They were deleted automatically when you drop the constraint.

The main difference between them is that a UNIQUE KEY cannot be added on view while a UNIQUE INDEX can be added in view.

Please watch the UNIQUE KEY video here and the UNIQUE Index video here for more details.

Q05. Are NULL values allowed in a UNIQUE KEY column?

Yes, NULL Value is allowed in the UNIQUE KEY column if it is nullable. But only one NULL value is allowed. If the UK is a Composite Unique Key, multiple NULL values can be allowed for all the best possible unique combinations of the composite columns.

Please watch the "SQL NULL Value" video here and the "SQL 3 Valued Logic" video here for more details. 

Q06. Can a UNIQUE KEY Contains an empty(blank) value or whitespace?

Yes, a single blank string or empty string is allowed in the Unique Key column as well. 

It is allowed in both numeric and string data type columns, but if it is a numeric type column by default 0 will be inserted.

Q07. Can a UNIQUE KEY column contain duplicate values?

In single-column (column level unique key) Unique Key, duplicate values are never allowed. 

However, if the Unique Key is a composite column (table level unique key), the duplicate value may be allowed in any individual composite column but the combination of all the composite columns must be always unique.

Q08. Can you add a Non-Clustered Index explicitly on any column in SQL?

Yes, we can create a Non-Clustered Index explicitly on any column including the UNIQUE KEY column as well. 

Please watch the NonClustered Index video here for more details.

Q09. Is it mandatory for the UNIQUE KEY to be a Non-Clustered Index? Can a UNIQUE KEY have a Clustered index?

No. It is not mandatory for the UNIQUE KEY to be a Non-Clustered Index. A Unique Key can be created on the Clustered Index as well. But you can create a UNIQUE INDEX on Clustered Index only if there is no Clustered Index created already either by Primary Key or CREATE INDEX command.

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

Q10. Can you create a UNIQUE KEY on a table that has already a Clustered Index?

Yes, If there is a Clustered Index already created on the table, a Unique Key can only be created on a NonClustered Index. There is no direct impact of an existing index for the Unique Key creation.

However, the interesting thing is that if the Index is already created on the same column, where you are creating the Unique Key, the SQL Engine never uses the existing index because it knows that the Index Created by CREATE INDEX command can be dropped explicitly by the user.

Please watch the Clustered Index video here.

Q11. Can you add both Clustered and Non-Clustered Indexes on a column?

Yes, we can do that. But only one Clustered Index is allowed in the table. So if the primary key is already using Clustered Index,  adding an explicit Clustered Index will not be allowed.

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

Q12. If a column contains a Non-Clustered Index, is it mandatory that the column must be UNIQUE KEY as well?

No, it is not required. Both UNIQUE KEY and Non-Clustered Index are two different and independent things. The confusion about this question is that UNIQUE KEY also creates a Non-Clustered Index implicitly.

Q13. Can you add multiple UNIQUE KEY or Non-Clustered Indexes on any table?

Yes, more than one Unique Key or Non-Clustered Index is allowed per table.

Please watch the Unique Key video here and the NonClustered Index video here for more details.

Q14. Can you create a UNIQUE KEY on the Temp Table?

Yes, we can create a Unique Key on a temp table. Please watch the Temp Table video here for more details.

Q15. Can you add a UNIQUE KEY on view?

No, we cannot create a Unique Key on view but Unique Index can be created on a view.

Please watch the Unique Index video here for more details.

Q16. What is the difference between table level and column level UNIQUE KEY Constraint?

Column-Level constraints refer to a single column in the table and do not specify a column name (except check constraints). They only refer to the column that they follow.

On the other hand, Table-Level constraints refer to one or more columns in the table. They specify the names of the columns to which they apply.

Column-level constraints are applied to the column only, whereas table-level constraints are used to define composite keys.

Q17. When the database table is in First Normal Form (1NF)?

Normalization is the process of minimizing redundancy from a relation or set of relations. Normal forms are used to eliminate or reduce redundancy in database tables. 

There are various levels of normalization. A database is in first normal form if it satisfies the following conditions:

• Contains only atomic values (Single Valued) i.e. table cannot hold multiple values.
• Attribute Domain does not change i.e all values should be of the same type.
• There are no repeating groups i.e two columns of a table should not be closely related.
• There is a unique name for every Attribute/Column.
• There is no duplicate record in the table.

Q18. Can you update or delete the UNIQUE KEY value of a table?

Yes, but not always. If the Unique Key column is referenced by the Foreign Key, then it cannot be either updated or deleted unless the referencing record of the child table is removed first.

Please watch the Foreign Key Constraint video here for more details.

Q19. Can you add the UNIQUE KEY Constraint on a column that contains data already?

Yes, but the column should not have any duplicate value. If a duplicate value is found, it will generate an error.

Q20. What is IAM (Index Allocation Map) Page in SQL?

IAM stands for Index Allocation Maps. IAM page is a page created by SQL for managing the data pages. It contains basically the metadata of the data. The page type of an IAM page is always 10.

The SQL Server uses IAM to track which pages have been allocated to which allocation unit. We can check that by DBCC command. Each allocation unit has at least one IAM Page; depending on the size of the table there might be many. Normally every index page has an associated IAM page.

Like every other page, an IAM Page has a 96-byte header. The page header is followed by a special index allocation map header. That header contains a base page address. It points to the first page that this allocation map covers.

Please watch the SQL 8KB Page Concept video here for more details about the IAM Page.

Q21. What is a Composite Key?

A composite key is composed of two or more columns that are collectively used to uniquely identify each and every record. The individual key of a composite key is known as the candidate key. 

Columns that make up the composite key can be of different data types. In composite key,  combined columns uniqueness is guaranteed, but when it is taken individually it does not guarantee uniqueness.

Q22. What is a Candidate Key?

A candidate key is a part of a super key that can uniquely identify a table record. Each individual column or attribute of a composite key is a candidate key.

In a table, we can have one or more than one candidate key, but we can use only one as a primary key for a table.  

Q23. What is an Alternate Key?

An alternate key is a secondary key. An alternate key is also a candidate key that has not been selected for the primary key.

If a table has more than one candidate key, one of them will become the primary key, and the rest of all are called alternate keys.

Q24. Can you add a UNIQUE Key Constraint on a nullable column?

Yes, A Unique Key always allows NULL Value by default. So it can be created on the nullable column. However, if you specify the NOT NULL explicitly on the Unique Key column, the UNIQUE KEY does that implicitly.

But if you have specified NOT NULL explicitly, the UNIQUE KEY keyword will generate an error if you insert NULL value.

Q25. How many UNIQUE KEYs are allowed in a table?

Definitely more than one UNIQUE KEYs are allowed in any table, unlike a Primary Key where only one Primary Key is allowed per table. 

But there is a limitation on the No of Unique Key as well per table. Only max 999+1 Unique Keys can be created on a table. There can be 999 Unique Key on the Non-Clustered Index and 1 Unique Key on the Clustered Index.  If you try to create more than that you will get an error.

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

Q26. How many NonClustered Indexes are allowed in any table?

Only one Clustered Index is allowed per table but 999 Non-Clustered Indexes are allowed in any table. 

Q27. Can you remove the index created by the Unique Key?

No, we cannot DROP the index created by the Unique Key. If you try to do so, you will get an error.

Q28. How can you Remove the Unique Key Constraint?

We can DROP the Unique Key Constraint by ALTER TABLE DROP CONSTRAINT command.

ALTER TABLE tblCustomer DROP CONSTRAINT UK_CustomerID;

Q29. Can you add UNIQUE KEY on a VARCHAR(MAX) column, why?

No, we cannot add a Unique Key on any MAX size data type either a VARCHAR(MAX) or NVARCHAR(MAX) column. 

The reason behind this is that an index cannot be created on these data types and a Unique Key requires an implicit index. And the reason behind, why the index can not be created on these columns is that they are stored in a LOB, not in an 8KB data page.

Please watch the SQL 8KB Page video here for more details.

Q30. Can you make the UNIQUE KEY column an Identity Column?

Yes, We can make the Unique Key Column as Identity Column as well. But generally, we use Primary Key as Identity Column.

Q31. Can you use UNIQUE KEY as a replacement for Primary Key? 

                                                -or-

If the Primary Key can uniquely identify every row, then why bother about UNIQUE KEY?

Not exactly. The purposes of both are different. PRIMARY KEY is used to uniquely identify each and every record of the table. While Unique Key is basically used to impose additional uniqueness on other columns of the table. The Primary Key is mandatory for a table if you are using any ORM Pattern for CRUD.

The Primary Key is basically used for internal users only for CRUD while the UNIQUE Key is for external users as they contain some meaningful information. And there can be multiple columns that require UNIQUENESS in the table. In that case, we can use only UNIQUE KEY for all the columns, not the Primary Key.

The last point is that Clustered Index is used by default for the Primary Key, while the Non-Clustered Index is used for the Unique Key by SQL.

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

Q32. Can you add UNIQUE KEY to the Primary Key column? 

Yes, we can add additional Unique Key Constraints on the Primary Key as well.

Please watch the SQL Unique Key video here for more details.

Q33. If a NULL value never equals other NULL values, it means they are unique. So why only one NULL value is allowed in the UNIQUE Key column?  

Yes, it is true that a NULL never equals itself. Actually NULL is not a value in SQL, it is an identifier for Known-Unknown. But coming to the main point of why only one is allowed. 

There is two way to deal with a NULL value in SQL i.e ANSI Standard and NON-ANSI Standard. In the case of NON-ANSI Standard SQL Query, we can use NULL value for filtering the records. In that case, if multiple NULL values were allowed, multiple records will be fetched against a single NULL value. That's why only one NULL value is allowed in Unique Key.

Please watch the SQL NULL Value here and the SQL Three Valued Logic video here for understanding the dilemma of NULL value in detail.

Q34. What is a HEAP in SQL?

A Heap table is a table in which, the data rows are not stored in any particular order within each data page. A heap is a table without any Clustered Index. The IndexID for a HEAP is 0 and a Clustered Table is 1. Since Heap is an unordered pile of stuff, a complete table scan happens every time.

Please watch the SQL 8KB Page video here and Clustered Index video here for more details about Heap Table vs Clustered Table.

Q35. Can you create a UNIQUE KEY on the table variable?

Yes, we can define a Unique Key on a table variable. But it must be specified at the time of declaration. 

Please watch the Table Variable video here for more details.

Q36. Can you create a UNIQUE  KEY on the Table Valued Type (TVP)?

Yes, we can define a Unique Key on Table-Valued Parameters as well. But it must be specified at the time of definition.

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

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 and Answers
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, Youtube, and Quora for regular updates.