How to create composite UNIQUE constraint with nullable columns?

Question

Let's say I have a table with several columns [a, b, c, d] which can all be nullable. This table is managed with Typeorm. I want to create a unique constraint on [a, b, c]. However this constraint does not work if one of these column is NULL. I can insert for instance [a=0, b= 1, c=NULL, d=0] and [a=0, b= 1, c=NULL, d=1], where d has different values.

With raw SQL, I could set multiple partial constraints (Create unique constraint with null columns) however, in my case, the unique constraint is on 10 columns. It seems absurd to set a constraint for every possible combination...

I could also create a sort of hash function, but this method does not seem proper to me?

Does Typeorm provide a solution for such cases?

Is there any value that can (or will) never appear in those columns (e.g. -999999) — user330315
– user330315, Commented Dec 6, 2019 at 10:29
From what you are describing, the unique constraint should be on a, b, c, and d. Why would the value of d have anything to do with the current unique constraint? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 6, 2019 at 10:30
@TimBiegeleisen To be more precise, I'm registering "prices" entities. In my case, d would be the price and a, b, c would be meta information and would stand for a unique identifier for the price. If I insert [a='US', b=NULL, c='USD', d=1000], I want this price to be updated if I insert [a='US', b=NULL, c='USD', d=500] later. So I can't put the unique constraint on a, b, c, d. @a_horse_with_no_name Indeed there are values which will never appear. However I think it's much cleaner to keep NULL for data which are meant to be unfilled instead of putting a custom predefined value? — Nicoowr
– Nicoowr, Commented Dec 6, 2019 at 11:11

user330315 · Accepted Answer · 2019-12-06 12:34:00Z

1

If you have values that can never appear in those columns, you can use them as a replacement in the index:

create unique index on the_table (coalesce(a,-1), coalesce(b, -1), coalesce(c, -1));

That way NULL values are treated the same inside the index, without the need to use them in the table.

If those columns are numeric or float (rather than integer or bigint) using '-Infinity' might be a better substitution value.

There is a drawback to this though:

This index will however not be usable for query on those columns unless you also use the coalesce() expression. So with the above index a query like:

select *
from the_table
where a = 10
  and b = 100;

would not use the index. You would need to use the same expressions as used in the index itself:

select *
from the_table
where coalesce(a, -1) = 10
  and coalesce(b, -1) = 100;

answered Dec 6, 2019 at 12:34

user330315

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

1 Comment

Nicoowr Over a year ago

Thanks for your answer @a_horse_with_no_name, it should work. On the principle, I still think there should be a way to do that without putting default not null values, but based on what I've read, I feel like I'll have to adopt a solution like yours :)

Collectives™ on Stack Overflow

How to create composite UNIQUE constraint with nullable columns?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related