This afternoon, I had to add a unique index for some table in the Sicirec PostgreSQL database. I had already assumed that I needed to use CREATE UNIQUE INDEX to create my new unique index until I noticed, thanks to pgAdmin‘s clear GUI, that some tables clearly had unique constraints and unique indexes while one table lacked a unique constraint and only had an unique index defined.
This motivated me to take a closer look at PostgreSQL’s documentation on unique indexes:
Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there’s no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.
Grepping for CREATE UNIQUE INDEX
in our migration history quickly revealed that the index which lacked an accompanying constraint was indeed created by CREATE UNIQUE INDEX instead of by ALTER TABLE ... ADD CONSTRAINT. So, now I know that, indeed, I have to use ALTER TABLE ... ADD CONSTRAINT instead of CREATE UNIQUE INDEX to add unique constraints with accompanying indexes to existing tables.
Rowan,
Comment on a very old post…
I have the opposite situation. From within pgAdmin, I can see a unique constraint, but *not* a unique index. However, it seems the constraint is backed by its own implicit index. Adding an explicit index to this column, however, yielded considerable query performance improvements.
I’m now more confused than I was before reading your post.
Any ideas (if you can remember back that far)?
–jon
Hi Jon,
Rereading the documentation part which I quoted in my post, I am inclined to conclude that you’ve stumbled upon a bug in either PostgreSQL or pgAdmin.
Maybe take it to the mailing list?
Here’s how a unique constraint shows in my version of pgAdmin:
Thanks for your interest in my old blog post,
Rowan