SQL Server Primary Keys

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

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