1

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?

Nicoowr
  • 770
  • 1
  • 10
  • 29
  • 1
    Is there any value that can (or will) never appear in those columns (e.g. `-999999`) –  Dec 06 '19 at 10:29
  • 1
    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 Dec 06 '19 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 Dec 06 '19 at 11:11

1 Answers1

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;
  • 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 :) – Nicoowr Dec 12 '19 at 13:16