SQL Server Primary Keys

Photo by XPS on Unsplash

Introduction

Hope you’re familiar with the concepts of what’s a primary key in a relational database management system like SQL Server. But, you need more details to better understand it, or you need some review or refresher for yourself, 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?

A primary key is a logical concept where 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?

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.

What’s a Primary Key Constraint?

A primary key constraint is a restriction that basically 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 the default clustered index if a clustered index on the table does not exist.

Composite Keys

Now, 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 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

Just a note, if you have 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

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 show 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!

--

--

--

husband, software engineer, reader, life long learner. https://jinnecesario.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Spring Data with a tap on Hibernate and a dash of JPA

Creating a DataWarehouse Using Amazon Redshift for StackOverflow Data

Prevent Flaky UI Tests from Killing Your CI

How to connect Wi-Fi 6E AP over the 6 GHz band with Ubuntu 21.04?

I could go on for hours, and so can you with your dictionary and a bit of

Distributed Tracing with Spring Cloud Jaeger

Terraform and the marvels of IaC, Part II

The principle of most availability

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jin Vincent Necesario

Jin Vincent Necesario

husband, software engineer, reader, life long learner. https://jinnecesario.com

More from Medium

STORED PROCEDURES IN SQL SERVER

Obfuscating data through Data Shuffle in Bulk

SQl Server Data Shuffle

SQL cross Join with Examples

Using the CASE Statement in SQL Server