PostgreSQL multi-column unique constraint and NULL values

by Manuel Leduc   Last Updated May 02, 2018 02:06 AM

I have a table like the following:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)

And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.

How can I guarantee my unique constraint even if id_C can be NULL in this case? Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?

Answers 3

You can do that in pure SQL. Create a partial unique index in addition to the one you have:

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

This way you enter for (a, b, c) in your table:

(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)

But none of these a second time.

Or use two partial UNIQUE indexes and no complete index (or constraint). The best solution depends on the details of your requirements. Compare:



    CREATE TABLE my_table (
       my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- for pg 10+
--     my_table_id bigserial PRIMARY KEY  -- for pg 9.6 or older
     , id_a int8 NOT NULL
     , id_b int8 NOT NULL
     , id_c int8
     , CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)

If you do not expect more than 2 billion rows (> 2147483647) over the lifetime of your table (including waste and deleted rows), consider integer (4 bytes) instead of bigint (8 bytes).

Erwin Brandstetter
Erwin Brandstetter
December 27, 2011 10:51 AM

A Null can mean that value is not known for that row at the moment but will be added, when known, in the future (example FinishDate for a running Project) or that no value can be applied for that row (example EscapeVelocity for a black hole Star).

In my opinion, it's usually better to normalize the tables by eliminating all Nulls.

In your case, you want to allow NULLs in your column, yet you want only one NULL to be allowed. Why? What kind of relationship is this between the two tables?

Perhaps you can simply change the column to NOT NULL and store, instead of NULL, a special value (like -1) that is known never to appear. This will solve the uniqueness constraint problem.

December 28, 2011 00:09 AM

I had the same problem and I found another way to have unique NULL into the table.

CREATE UNIQUE INDEX index_name ON table_name( COALESCE( foreign_key_field, -1) )

In my case, the field foreign_key_field is a positive integer and will never be -1.

So, to answer Manual Leduc, another solution could be

CREATE UNIQUE INDEX  u_constrainte UNIQUE(COALESCE(id_a, -1), COALESCE(id_b,-1),COALESCE(id_c, -1) )

I assume that ids won't be -1.

What is the advantage on creating a partial index ?
In case where you don't have the NOT NULL clause, id_a, id_b and id_c can be NULL together only once.
With a partial index, the 3 fields could be NULL more than once.

Luc M
Luc M
May 17, 2012 20:57 PM

Related Questions

Why does a UNIQUE constraint allow only one NULL?

Updated January 15, 2018 15:06 PM

Postgresql : can column uniqueness be commutative

Updated June 28, 2015 13:02 PM