PostgreSQL Composite Primary Keys

Introduction

In this article we’ll be discussing composite primary keys in PostgreSQL. Primary keys are a way to uniquely identify a record. More often than not we use simple arbitrary id numbers that progress sequentially whenever a new record is created. But in certain scenarios you don’t need an arbitrary number to guarantee uniqueness, you can instead use a combination of values in the columns to do it. This is the idea we’ll be exploring in this article so please read on.

What is a Composite Key

Sometimes you can guarantee uniqueness of a record through a combination of column values. This is what a composite key is. It’s a key formed by joining multiple column values that guarantee uniqueness.

Example of a Composite Key

Let’s take the example of a table of records called course_grades that holds the history of courses students at a university have taken including the student_id, quarter_id, course_id, and grade.

We’ll write the sql for this later but the data would look something like this:

Without any id numbers it can be hard to figure out uniqueness. We can see that quarter_id’s are duplicated so that is not unique. We can see that courses are duplicated so that’s not unique. We can also see that students have taken several courses in different quarters so that’s not enough to guarantee uniqueness. Each of these individually cannot guarantee uniqueness.

So how do we solve this?

To guarantee uniqueness in a situation like this we can combine quarter_id, course_id, and student_id. The combination of those will always be unique.

Let’s see how we’d do that in sql and start by creating the table:

Notice how we’ve created the primary key just by passing in the columns that we need to guarantee uniqueness.

Conclusion

We’ve discussed that the composite primary keys is a use case when entries in your dataset can’t be uniquely identified by a single column value but a combination of column values can. The use of composite primary keys is a fairly advanced topic but it can come in handy depending if your dataset permits it.

If your having trouble optimizing your database and want to turn the reigns over to a company that deals with it everyday please reach out to Object Rocket. We’re happy to hear about your use case and advise on solutions.

Just the Code

Again here’s the syntax for creating a composite key in PostgreSQL.