SQL Server Primary Keys

Introduction

I hope you're familiar with the primary key in a relational database management system like SQL Server. But, if you need more details to understand it better or need some review or refresher, you have come to the right place.

This article will discuss a primary key, entity integrity, primary key constraint, composite keys, and the benefits of a primary key.

Let's get started.

What's A Primary Key in SQL Server?

A primary key is a logical concept in which a column or field uniquely identifies each record inside a database table. Moreover, it has multiple attributes: it must not be null, and it must be unique.

Easy to remember, right? But if you're curious, we'll test and see what will happen when we try to pass a NULL and duplicate value in a primary column in the latter part of the article.

What's Entity Integrity in SQL Server?

Entity integrity is a rule for practical database construction, and this practice is widely used. It is a process of enforcing the primary key for each table in a database.

Therefore, it is implemented to uniquely identify each row inside the table by specifying a primary key inside our database table. Find a detailed article about Entity Framework in SQL Server here.

What's a Primary Key Constraint?

A primary key constraint is a restriction that ensures entity integrity.

Note: Unique constraint also ensures entity integrity.

Benefits of Primary Key and Entity Integrity

Proper usage and selection of a primary key and maintaining entity integrity prevent duplicate records and other issues that would indirectly compromise the integrity of the database.

Things to Remember About Primary Keys

• A primary key column cannot have NULL values.
• A primary key column cannot have duplicate values.
• A table can have only one primary key constraint.
• When multiple columns are used as primary keys, they are called composite keys.
• It is good to remember that a primary key is a default clustered index if a clustered index on the table does not exist.

Composite Keys

You might ask why I see multiple primary keys in one table. Those are called composite keys.

Composite keys use two or more fields from a table to create a unique value.

Therefore, it guarantees uniqueness only when combined columns, but they don't guarantee uniqueness individually.

Creating A Primary Key on a Table

Let's show how we can define a column as a primary key.

CREATE TABLE EMPLOYEE(
[Id] int NOT NULL IDENTITY(1,1), 
[FirstName] nvarchar(50), 
[LastName] nvarchar(50), 
[Age] int, 
PRIMARY KEY (Id))

In the code sample above, we have seen that using the PRIMARY KEY and then passing the column Id, we have defined the primary key of the EMPLOYEE table.

Let's try to see the result below.

Add a Primary Key on A Table

Let's recreate the table from the previous example, but we will not create the primary key.

We will create a primary key after we have created the table.

Let's try to see the code sample below.

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN 
	DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
	[Id] int NOT NULL IDENTITY(1,1), 
	[FirstName] nvarchar(50), 
	[LastName] nvarchar(50), 
	[Age] int);
END 
GO 
BEGIN 
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

If you removed the IDENTITY (1,1), everything will still be good and have no errors because it's an auto-incrementing column.

Although these two are used together, there's no requirement when defining a primary key column that it needs to be an identity column.

Let's try to see an example below.

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN 
	DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
	[Id] int NOT NULL, 
	[FirstName] nvarchar(50), 
	[LastName] nvarchar(50), 
	[Age] int);
END 
GO 
BEGIN 
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Again, another note, if we have forgotten the NOT NULL that makes the Id column nullable, it will give you an error.

You'll probably see an error like this.

"Cannot define a PRIMARY KEY constraint on nullable column in table 'EMPLOYEE.'"

Let's try to see an example below.

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN 
	DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
	[Id] int, -- NOT NULL REMOVED FOR YOU TO SEE THE ERROR MESSAGE
	[FirstName] nvarchar(50), 
	[LastName] nvarchar(50), 
	[Age] int);
END 
GO 
BEGIN 
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Let's try to see the result below.

Delete Primary Key on A Table

In this section, let's try to recreate the table again, but after creating the table, let's make a primary key with the name of [PK_ON_EMPLOYEE_TABLE].

The reason for giving the primary key a custom name is so we won't have a hard time knowing its name when we need to drop the primary key.

Let's try to see the example below.

PRINT 'STEP 0. DROP EMPLOYEE TABLE IF EXISTS'

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN 
	PRINT 'STEP 0.1 DROPPING EMPLOYEE TABLE'
	DROP TABLE EMPLOYEE;
END
GO

PRINT 'STEP 1. CREATE THE TABLE'

BEGIN
CREATE TABLE EMPLOYEE(
	[Id] int NOT NULL IDENTITY(1,1), 
	[FirstName] nvarchar(50), 
	[LastName] nvarchar(50), 
	[Age] int);
PRINT 'STEP 1.1 EMPLOYEE TABLE CREATED'
END 
GO 

BEGIN 
PRINT 'STEP 2. EMPLOYEE TABLE ADDING PRIMARY KEY [PK_ON_EMPLOYEE_TABLE]'
ALTER TABLE EMPLOYEE ADD CONSTRAINT [PK_ON_EMPLOYEE_TABLE] PRIMARY KEY(Id);
END
GO

BEGIN
PRINT 'STEP 3. EMPLOYEE TABLE REMOVING THE PRIMARY KEY'
ALTER TABLE EMPLOYEE 
DROP CONSTRAINT [PK_ON_EMPLOYEE_TABLE];
END

Let's try to see the result below.

Inserting NULL values into Primary Key Column

This obviously will show an error because we're violating the primary key constraint.

Still, we'll see how the SQL Server will react when inserting NULL values out of curiosity.

Let's try to see an example below.

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN 
	DROP TABLE EMPLOYEE;
END
GO
BEGIN

CREATE TABLE EMPLOYEE(
	[Id] int NOT NULL, 
	[FirstName] nvarchar(50), 
	[LastName] nvarchar(50), 
	[Age] int);
END 
GO 

BEGIN 
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id) ;
END
GO 

BEGIN 
--LET'S INSERT NULL value and expect an error
INSERT INTO [dbo].[EMPLOYEE] ([Id],[FirstName],[LastName],[Age])
VALUES 
(NULL, 'Jin', 'Necesario', 100)
END
GO

Let's try to see the result below.

Inserting Duplicate Values into Primary Key Column

Again, this will obviously be an error because we're violating the primary key constraint. Still, we'll see how the SQL Server will react when inserting duplicate values out of curiosity.

Let's try to see an example below.

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN 
	DROP TABLE EMPLOYEE;
END
GO
BEGIN

CREATE TABLE EMPLOYEE(
	[Id] int NOT NULL, 
	[FirstName] nvarchar(50), 
	[LastName] nvarchar(50), 
	[Age] int);
END 
GO 

BEGIN 
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id) ;
END
GO 

BEGIN 
--LET'S INSERT SAME Id value of 1 and expect an error
INSERT INTO [dbo].[EMPLOYEE] ([Id],[FirstName],[LastName],[Age])
VALUES 
(1, 'Jin', 'Necesario', 100),
(2, 'Vincent','Necesario', 100),
(1, 'Jin Vincent','Necesario', 100)
END
GO

Let's try to see the result below.

Summary

In this article, we have discussed the following,

• What's A Primary Key?
• What's Entity Integrity?
• What's a Primary Key Constraint?
• Benefits of Primary Key and Entity Integrity
• Things to Remember About Primary Keys
• Composite Keys
• Creating A Primary Key on A Table
• Add a Primary Key on A Table
• Delete Primary Key on A Table
• Inserting NULL values into Primary Key Column
• Inserting Duplicate Values into Primary Key Column

I hope you have enjoyed this article.

Once again, I hope you have enjoyed this article/tutorial as I have enjoyed writing it.

Stay tuned for more. Until next time, happy programming! Please don't forget to bookmark, like, and comment. Cheers! and Thank you!