Thoughts on Column(unique=True, index=True) creating a unique constraint for postgres?

[Jonathan Beluch]

Not sure about other DBs, but according to pg docs [1], it's preferred to make a unique constraint (and know that you get the index for free) versus creating a unique index. 

Knowing this for pg, you can just do unique=True and get the constraint and the index. I realize this is a very small implementation detail that probably doesn't matter but wanted to surface it here in case it's something you might want to change?

[1] http://www.postgresql.org/docs/9.0/static/indexes-unique.html

[Mike Bayer]

On 02/09/2016 04:47 PM, Jonathan Beluch wrote:
> Not sure about other DBs, but according to pg docs [1], it's preferred
> to make a unique constraint (and know that you get the index for free)
> versus creating a unique index.

I'm not seeing the word "preferred" in that document? It is actually
simpler from a database introspection perspective to create a UNIQUE
INDEX alone, the UNIQUE CONSTRAINT is redundant on the Postgresql platform.

>
> Knowing this for pg, you can just do unique=True and get the constraint
> and the index. I realize this is a very small implementation detail that
> probably doesn't matter but wanted to surface it here in case it's
> something you might want to change?
>
> [1] http://www.postgresql.org/docs/9.0/static/indexes-unique.html
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

On Tuesday, February 9, 2016 at 6:20:30 PM UTC-5, Michael Bayer wrote:

I'm not seeing the word "preferred" in that document?    It is actually
simpler from a database introspection perspective to create a UNIQUE
INDEX alone, the UNIQUE CONSTRAINT is redundant on the Postgresql platform.

It's in the note/callout. see below...

> 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.

The postgres developers often jump to correct anyone who dares post an example with a 'unique index' on a create table [in their mailing list].

I still create the unique index though... 

https://frinkiac.com/?p=caption&q=spite&e=S13E03&t=863112&m=+This+is+pretty+far+to+go%0A+just+to+spite+Moe.+It%27s%0A+not+about+spite.+It%27s%0A+about+petty+revenge