1

I am having a table with two columns. Want to add unique constraint on both columns. One of the column is nullable.

I m trying with this syntax: 

ALTER TABLE a ADD CONSTRAINT a_unq UNIQUE (a_id, (coalesce(a_name, '')));

Its prompting error at or near "("

learner
  • 21
  • 1
  • 7
  • m confused, why are you using coalesce?? also if possible please confirm the db server you are using newer sql(2008+) server allows you to create Unique constraints with where clause which can be then used conditionally. – BlindSniper Dec 07 '17 at 05:38
  • Create partial constraint(Refer - https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns) – learner Oct 09 '19 at 11:44

2 Answers2

0

If you uactually need the solution was in PostgreSQL multiple nullable columns in unique constraint : just create UNIQUE index on table, it will stop inserts even if it isn't a constraint declared in table definition:

CREATE UNIQUE INDEX ex_12345 ON example
        (coalesce(FIELD1, -1)
        , coalesce(FIELD2, -1)
        , coalesce(FIELD3, -1)
        , coalesce(FIELD4, -1)
        , coalesce(FIELD5, -1)
        )
        ;
user158037
  • 2,659
  • 1
  • 24
  • 27
-3

You don't need to use coalesce in that case. Just create constraint in the usual way:

ALTER TABLE a ADD CONSTRAINT a_unq UNIQUE (a_id, a_name);
D.Zotov
  • 2,044
  • 2
  • 14
  • 28
  • 2
    actually one of his column is nullable that's why I think he tried to use coalesce. I think your query will not work in this scenario as it will not allow more that on null values – BlindSniper Dec 07 '17 at 05:43
  • Why do you think that the constraint will not allow more than one NULL value? – Laurenz Albe Dec 07 '17 at 06:26
  • @BlindSniper: if that was the case (which it isn't) then `coalesce()` would actually do the wrong thing as you replace null with `''` which indeed means you can have only one combination of a_id and a null value. without coalesce you can add `(1,null), (1,null)` but _with_ coalesce that would be turned into putting `(1,'')` into the index and _that_ will only be allowed once –  Dec 07 '17 at 06:48