0

In Postgres 11 if I add a unique index IX_A_B on 2 columns (A,B)
is that enough to ensure no duplicate pairs can be inserted
say (5,4) on one row, and (5,4) on another row?

Or... do I need to also add a unique constraint based on the unique index?

ALTER TABLE TBL    
ADD CONSTRAINT unique_A_B 
UNIQUE USING INDEX IX_A_B;

Also, if I don't need the unique constraint then...
what is the purpose of having such constraints at all?

NOTE: I just tried it, seems the constraint is not needed.
Then I am confused... what is the idea behind unique constraints,
and behind this syntax/command above in particular?

peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • Or maybe [PostgreSQL: unique constraint or unique index](https://stackoverflow.com/q/51003943/479863), [Unique constraint vs. unique index?](https://stackoverflow.com/q/32886581/479863), [Should I specify both INDEX and UNIQUE INDEX?](https://stackoverflow.com/q/4531693/479863), [Does a Postgres UNIQUE constraint imply an index?](https://stackoverflow.com/q/29655439/479863), ... – mu is too short Feb 17 '21 at 19:39
  • Thanks for the links, I will take a look and delete my question. Sorry. – peter.petrov Feb 17 '21 at 19:40
  • It may just be a matter of completeness and preference. By preference I always create a constraint not just an index. – Belayer Feb 17 '21 at 20:38

0 Answers0