SQL PRIMARY KEY Constraint

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

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

A Primary Key is nothing but a constraint which ensures that each and every record is unique and can be identified uniquely in an isolated manner. 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 as well.

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

A user can define constraints either at column level or table level. The column-level constraints apply only to a specified 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.

PRIMARY KEY Constraint

The PRIMARY KEY is nothing but a Constraint that is used to uniquely identify each individual row or record of the table. It can be either a single column or a composite column. The purpose of the PRIMARY KEY is to enforce Entity Integrity in the table.

A PRIMARY KEY is a value that is unique for every record of the table and a record can be accessed by the Primary Key value directly without affecting any other record of the table.

PRIMARY KEY Fundamental Points

1. There can be one and only one PRIMARY KEY Constraint in any table. A PRIMARY KEY Constraint can be a Composite Primary Key as well. If you try to create more than one Primary Key on a table you will get the below error:

Msg 1902, Level 16, State 3, Line 8
Cannot create more than one clustered index on table 'tblEmployee'. Drop the existing clustered index 'PK_ID' before creating another.

2. The PRIMARY KEY Constraint guarantees that no NULL or Duplicate Values are allowed (*See Misconceptions Exceptions). If you try to insert any duplicate value in the Primary Key you will get the below error:

Msg 2627, Level 14, State 1, Line 17
Violation of PRIMARY KEY constraint 'PK_ID'. Cannot insert duplicate key in object 'dbo.tblEmployee'. The duplicate key value is (1).
The statement has been terminated.

3. A PRIMARY KEY never creates a Separate Index Data Page, unlike UNIQUE 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.

If you see the PageType = 1 is nothing but the data page and IndexID = 1 confirms that it is a Clustered Index as well.

On the other hand for the IX_EmpName Secondary Index, there is a separate IAM Page (PageType = 10) and Index Page (IndexID = 2) is created.

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

4. A PRIMARY KEY is usually created for internal use only. Generally, they are not useful for external end-users. The only purpose is to identify each record in the table internally for DML operations like SELECT, INSERT, UPDATE, and DELETE. Apart from this Primary Key is also used in the Foreign Key Reference.

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

Msg 547, Level 16, State 0, Line 29

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

The statement has been terminated.

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

Msg 547, Level 16, State 0, Line 31

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

The statement has been terminated.

Please watch the FOREIGN KEY video here for more details.

6. All the Relational Database Management Systems support Primary Keys. In fact, for a table to be considered a relational table, and in the First Normal Form (1NF), it must have a Primary Key. No SQL Database like COSMO, there is no concept of Primary Key. They use something called Partition Key.

7. When multiple columns are used as a Primary Key, it is known as a Composite Primary Key. In the case of designing the Composite Primary Key, you should use as few columns as possible because the more columns you use for the Primary 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,

PRIMARY KEY CLUSTERED (CustomerID, ProductID)

);

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

8. The Primary Key can be added while designing the table by CREATE TABLE statement.

CREATE TABLE tblEmployee

( ID INT NOT NULL CONSTRAINT PK_ID PRIMARY KEY CLUSTERED,

Name VARCHAR(50)

);

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

ALTER TABLE tblEmployee ADD CONSTRAINT PK_ID PRIMARY KEY CLUSTERED(ID);

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

Msg 1505, Level 16, State 1, Line 11
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tblEmployee' and the index name 'PK_ID'. The duplicate key value is (1).
Msg 1750, Level 16, State 0, Line 11
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 Primary Key Constraint.

Please watch the UNIQUE INDEX video here for more details.

10. A Temp Table can define a Primary Key either while creating the Temp Table by CREATE TABLE command or by ALTER TABLE statement as well. This is one of the most common confusing Interview Questions of SQL.

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

11. A Table Variable can also define a Primary Key but only at the time of declaration. You cannot add a Primary 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.

12. A Table-Valued Type (TVP) can also have a Primary Key but it must be specified only at the time of declaration. You cannot add a Primary 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.

13. A PRIMARY KEY 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:

The reason behind the above error is that the column (EmpName) is invalid for creating an index because an index can not 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.

14. A PRIMARY KEY cannot be created on Nullable Column, either by CREATE TABLE Command or by ALTER TABLE Command. If you try to do so on the ID column of the above table, you will get the below error:

Msg 8111, Level 16, State 1, Line 3

Cannot define PRIMARY KEY constraint on nullable column in table 'tblEmployee'.

Msg 1750, Level 16, State 0, Line 3

Could not create constraint or index. See previous errors.

15. You cannot DELETE or DROP the Index created by the Primary Key explicitly. It will only be deleted when you DROP the Primary 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 4, Line 12
An explicit DROP INDEX is not allowed on index 'tblEmployee.PK_ID'. It is being used for PRIMARY KEY constraint enforcement.

16. A PRIMARY 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.

Common Misconceptions About PRIMARY KEY

There are some misconceptions about the PRIMARY KEY among the developers and the interviewee. Here we will see some common misconceptions and the real truth about them.

Misconception 1: BLANK (EMPTY) VALUE or WHITESPACE ARE NOT ALLOWED IN PRIMARY KEY LIKE NULL

One misconception about the Primary Key is that a Blank Value cannot be inserted into the PRIMARY KEY. But it is not true anymore. The reason behind this misconception is that people confuse the NULL value with an empty string or zero.

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

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

If the data type of the Primary Key column is numeric, by default 0 will be inserted in the place of blank or empty string. On the other hand, if the Primary 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 Primary Key column, you will get a constraint violation error.

Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK__tblEmplo__3214EC27C1972453'. Cannot insert duplicate key in object 'dbo.tblEmployee'. The duplicate key value is ().
The statement has been terminated.

But you cannot insert any NULL value into the Primary Key at all, even a single NULL value. If you try to do so, you will get the below error:

Msg 515, Level 16, State 2, Line 26
Cannot insert the value NULL into column 'ID', table 'TechPoint.dbo.tblEmployee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Please watch the Primary Key video here for a live demo.

Misconception 2: PRIMARY KEY NEVER ALLOWS DUPLICATE VALUES

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

We know that a PRIMARY KEY has combined features of both NOT NULL Constraint and UNIQUE KEY Constraint together. And the primary use of the Primary Key is to uniquely identify the individual row of the table.

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

Please watch the UNIQUE KEY video here for more details.

Misconception 3: PRIMARY KEY ALWAYS CREATES CLUSTERED INDEX

Another misconception about the Primary Key is that a PRIMARY KEY always creates CLUSTERED INDEX for the Primary Key Column. But it is not always true.

Actually, by default, a PRIMARY KEY Constraint creates the CLUSTERED INDEX if there is no Clustered Index defined already in the table. But if there is any CLUSTERED INDEX defined already in the table, it creates a NON-CLUSTERED INDEX forcefully.

Please watch the CLUSTERED INDEX video here, and the NON-CLUSTERED INDEX video here for more details. You can also watch the Clustered Index vs Non-Clustered Index video here.

You can use the below SQL Query to check the existing Constraints and Indexes details of any table:

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

EXEC sp_helpindex 'dbo.tblEmployee';

Misconception 4: NO EXPLICIT INDEX CAN BE CREATED ON PRIMARY KEY COLUMN

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

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

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

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

Misconception 5: SQL ENGINE ALWAYS STORES THE DATA PHYSICALLY BASED ON THE PRIMARY KEY COLUMN

There is one more interesting misconception is that a PRIMARY KEY always stores the data physically based on the PRIMARY KEY. But it is not always true.

The data is basically stored based on the CLUSTERED INDEX on the data page. If the PRIMARY KEY is on a NON-CLUSTERED INDEX data will not be stored physically based on the PRIMARY KEY. Because in that case, the table will be a HEAP table.

Please watch the SQL 8KB Page Rule video here and How the Data is Stored Physically in SQL video here for more details.

Misconception 6: PRIMARY KEY TURNS THE TABLE FROM HEAP TO NON-HEAP (CLUSTERED TABLE)

This is the most common misconception that a PRIMARY KEY converts the HEAP table into a NON-HEAP (Clustered) table. But it is not always true actually.

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 7: PRIMARY KEY SHOULD ALWAYS BE CLUSTERED INDEX

This is also a misconception that a PRIMARY KEY should always be a CLUSTERED INDEX. But it is not necessary. It is not mandatory that a Primary Key should always be a Clustered Index.

We generally keep the Primary Key on the Clustered Index because the data is basically physically stored on the order of the Clustered Index. So it makes the data searching and retrieval very fast compared to the secondary indexes.

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 some other column that is not a Primary Key you can make that column as Clustered Index instead of Primary Key column.

Misconception 8: PRIMARY KEY AND CLUSTERED INDEX ARE THE SAME

One more famous misconception is that the Primary Key and 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 PRIMARY KEY creates a 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, PRIMARY 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: PRIMARY KEY CAN NOT HAVE EXPLICIT SECONDARY INDEX

One more famous misconception is that the Primary Key cannot have any user-defined custom explicit index. But it is not true.

A column can have multiple indexes, so the Primary Key column as well. But if there is a Clustered Index defined on any column of the table, you can not define any other Clustered Index on any column.

So you are free to create the Non-Clustered Index on any column including the Primary Key which has a Clustered Index already based on your requirement.

Please watch the Secondary Index video here for more details.

Misconception 10: PRIMARY KEY SHOULD ALWAYS BE AN IDENTITY COLUMN

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

Primary Key and IdentityColumn both are two different things. It is not mandatory that a Primary Key should also be 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.

If you accept the user input value for the Primary Key column, it might always be possible that there is a Primary Key violation that happens frequently as users don't know the next unique value for the Primary Key.

Conclusion

Primary Key is a constraint that is used to uniquely identify each and every record of the table.

Primary Key can be created on both Clustered and Non-Clustered Index. But it can not be created on a nullable column at all.

NULL values are not allowed but Blank value is allowed in a Primary Key.

Sunday, April 3, 2022

SQL PRIMARY KEY Constraint