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...
Table of Contents
The following example shows both a primary key and a unique constraint:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
test=# CREATE TABLE t_sample (a int PRIMARY KEY, b int UNIQUE); CREATE TABLE test=# d t_sample Table 'public.t_sample' Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | not null | b | integer | | | Indexes: 't_sample_pkey' PRIMARY KEY, btree (a) 't_sample_b_key' UNIQUE CONSTRAINT, btree (b) |
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.
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:
|
1 2 3 |
test=# INSERT INTO t_sample VALUES (1, NULL); INSERT 0 1 |
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:
|
1 2 3 4 |
test=# INSERT INTO t_sample VALUES (NULL, 2); ERROR: null value in column 'a' of relation 't_sample' violates not-null constraint DETAIL: Failing row contains (null, 2). |
This is actually the single biggest difference between these two types of constraints. Keep that in mind.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
test=# CREATE TABLE t_fk_1 ( id serial PRIMARY KEY, aid int REFERENCES t_sample (a) ); CREATE TABLE test=# CREATE TABLE t_fk_2 ( id serial PRIMARY KEY, bid int REFERENCES t_sample (b) ); CREATE TABLE test=# d t_fk_1 Table 'public.t_fk_1' Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ id | integer | | not null | nextval('t_fk_1_id_seq'::regclass) aid | integer | | | Indexes: 't_fk_1_pkey' PRIMARY KEY, btree (id) Foreign-key constraints: 't_fk_1_aid_fkey' FOREIGN KEY (aid) REFERENCES t_sample(a) test=# d t_fk_2 Table 'public.t_fk_2' Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+------------------------------------ id | integer | | not null | nextval('t_fk_2_id_seq'::regclass) bid | integer | | | Indexes: 't_fk_2_pkey' PRIMARY KEY, btree (id) Foreign-key constraints: 't_fk_2_bid_fkey' FOREIGN KEY (bid) REFERENCES t_sample(b) |
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.
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.
+43 (0) 2622 93022-0
office@cybertec.at
b column may be is not null. In this case, there will be no differences from the primary key (field a)?
Hi I need to migrat DB2 to PG , as DB2 have alias as similarly things we have in PG
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.