Unique constraints on multiple columns that cannot both be null

Question

I wanted to ask if there's a better way of modeling the following behavior in Postgres 10:

CREATE TABLE test.my_table
(
    id   UUID PRIMARY KEY,
    id_a UUID,
    id_b UUID,
    some_shared_data JSONB,
    UNIQUE (id_a, id_b)
);

CREATE UNIQUE INDEX IF NOT EXISTS b_null_constraint ON test.my_table (id_a) WHERE id_b IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS a_null_constraint ON test.my_table (id_b) WHERE id_a IS NULL;

ALTER TABLE test.my_table
    ADD CONSTRAINT both_null_constraint CHECK (
        (id_b IS NOT NULL) OR (id_a IS NOT NULL));

I.e. the constraints are:

Both id_a and id_b cannot be null
The combination of id_a and id_b must be unique (including cases where one of them is null)

It feels to me the code above to set this up is not very expressive. Would people do this in another/more normalized way? I tried splitting this up in separate tables but then constraint (1.) is hard to satisfy.

This looks just fine to me.

Laurenz Albe
– Laurenz Albe

2019-08-26 15:33:18 +00:00
Commented Aug 26, 2019 at 15:33 — Laurenz Albe
– Laurenz Albe, Commented Aug 26, 2019 at 15:33

Gordon Linoff · Accepted Answer · 2019-08-26 16:05:36Z

1

It is possible to do this with just two unique constraints. The second one is:

CREATE UNIQUE INDEX IF NOT EXISTS ab_null_constraint ON my_table ( coalesce(id_a, id_b), (id_a is null) WHERE id_a IS NULL or id_b is null;

Here is a db<>fiddle.

Actually, you can combine all this into one unique index:

CREATE UNIQUE INDEX IF NOT EXISTS ab_null_constraint ON
     my_table ( coalesce(id_a, id_b),
                coalesce(id_b, id_a),
                (id_a is null),
                (id_b is null)
              );

Here is a db<>fiddle for this.

You might find your original formulation more maintainable.

edited Aug 26, 2019 at 16:05

answered Aug 26, 2019 at 16:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonas.z Over a year ago

Thank you for the input, great to just get another perspective! I think the second example is not that bad actually. One thing I was skeptical about regarding my original solution was that I found it used a lot of different keywords for essentially the same thing. You solution at least sort of fixes that! I think I like it better:)

Gordon Linoff Over a year ago

@Jonas.z . . . Put a comment next to the index explaining what you want it to do. It is not obvious (at least to me), and I wrote the code.

Jonas.z Over a year ago

Yes, definitely. I find it useful to partition these migration-type sql files with rigorous documentation. What I liked with your solution is that it's an encapsulated expression for what's happening. I.e. I can put comments above referring to it directly

Collectives™ on Stack Overflow

Unique constraints on multiple columns that cannot both be null

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related