Primary Key in SQL with PostgreSQL

Introduction

In this article we’ll cover what a primary key is in SQL programming languages and go over a concrete example using PostgreSQL.

What is a Primary Key

A primary key is a column in a table which must have a unique value so that each row in a table has at least one unique identifier that allows it to be uniquely identified. If it’s useful, think of this like a social security number where no two people have same social security number. It is unique to you.

The primary key also has the requirement that it is not null.

You should also know that there can be only one primary key per table although other columns can have constraints that require they also be unique and/or not null.

How to create a PRIMARY KEY

Let’s create a primary key on a new table. Let’s imagine we are creating a database for a small grocery store and creating a table for all the products. We want each product to have their own id number (INTEGER) which will be unique primary key. Let’s see how we might do that:

It’s that simple. We’ve created a table with a primary key.

Now let’s try adding some entries into our products table.

Let’s query the table to make sure it worked:

It sure did!

Errors

Let’s go through and make some intentional errors to see what happens. First let’s add another product with the exact same id:

Result:

Perfect. We know our primary key is working because it won’t let us set another product with a duplicate id. This is exactly what we want.

Common Usage

You’ll commonly see tables created with PRIMARY KEYS that also have the SERIAL type that will have the auto-incrementing feature so that every time you add a new row you don’t need to specify the id, instead it will just get updated to the next number.

We could redo the example like this:

Then let’s insert some data and see what happens to the id’s:

Query all products in the table:

Notice how the ids started at 1 and are incrementing sequentially by themselves. This is a very common practice. It’s also, as you’ve seen, super easy to implement and you don’t have to specify ids when inserting new rows; instead they get assigned when they are added.

Conclusion

In this article we talked about why primary keys are used in sql databases to make sure rows can always be uniquely identified. We then showed a very simple example of using a primary key when creating a table and then a more practical use with the SERIAL type.

We hope this short tutorial helped. There are more advanced options with primary keys so stay tuned for more information from Object Rocket. As always if you’re stressing about how to setup your database, how to manage it, or whether you should migrate, please don’t hesitate to ask.