1. /
  2. Latest Articles
  3. /
  4. Data
  5. /
  6. SQL Server
  7. /
  8. SQL Server Primary Keys

Viewed 42 times

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.

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.

Creating A Primary Key on A Table

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.

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.

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.

Let’s try to see the result below.

primary key constraint nullable column

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.

Let’s try to see the result below.

deleting primary key

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.

Let’s try to see the result below.

insert_null_into_primary_key

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.

Let’s try to see the result below.

inserting_duplicate_values_primary_key

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.

This article was originally written and posted here.

Stay tuned for more. Until next time, happy programming!

Please don’t forget to bookmark, like, and comment.

Cheers! and Thank you!

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
© All Rights Reserved 2020
0
Would love your thoughts, please comment.x