CYBERTEC PostgreSQL Logo

Primary Keys vs. UNIQUE Constraints in PostgreSQL

11.2021 / Category: / Tags: |

Most of my readers will know about primary keys and all kinds of table constraints. However, only a few of you may have ever thought about the difference between a primary key and a UNIQUE constraint. Isn’t it all just the same? In both cases, PostgreSQL will create an index that avoids duplicate entries. So what is the difference? Let's dig in and find out...

What primary keys and UNIQUE constraints do

The following example shows both a primary key and a unique constraint:

The really important observation is that both features make PostgreSQL create an index. This is important because people often use additional indexes on primary keys or unique columns. These additional indexes are not only unnecessary, but actually counterproductive.

The key to success: NULL handling

What makes a primary key different from a unique index is the way NULL entries are handled. Let's take a look at a simple example:

The example above works perfectly. PostgreSQL will accept the NULL value for the second column. As long as the primary key contains a unique value, we are OK. However, if that changes, then an error will occur:

This is actually the single biggest difference between these two types of constraints. Keep that in mind.

Using foreign keys

The next logical question which arises is: What does that mean for foreign keys? Does it make a difference? Can we reference primary keys as well as unique constraints?

The simple answer is yes:

It's perfectly acceptable to reference a unique column containing NULL entries, in other words: We can nicely reference primary keys as well as unique constraints equally - there are absolutely no differences to worry about.

If you want to know more about NULL in general, check out my post about NULL values in PostgreSQL.

Finally...

Primary keys and unique constraints are not only important from a logical perspective, they also matter from a database-performance point of view. Indexing in general can have a significant impact on performance. This is true for read as well as write transactions. If you want to ensure good performance, and if you want to read something about PostgreSQL performance right now, check out our blog.

3 responses to “Primary Keys vs. UNIQUE Constraints in PostgreSQL”

  1. There are also expectations of tools in the ecosystem if I remember correctly.
    Some administration tools (ex: Datagrip) need a PK to let you edit rows.
    Some migration tools (ex: pg_repack, AWS DMS) need a PK to work.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram