From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Unique index and unique constraint
Date: Fri, 26 Jul 2013 18:19:37 -0400
Message-ID: <20130726221937.GL14652@eldon.alvh.no-ip.org> (raw)
In-Reply-To: <CAAY=A78V1gCxC6VEU4_G8e0XouT=c64RA2GaSfGE8MEy3=33_g@mail.gmail.com>
References: <CAAY=A78V1gCxC6VEU4_G8e0XouT=c64RA2GaSfGE8MEy3=33_g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
JORGE MALDONADO escribió:
> I guess I am understanding that it is possible to set a unique index or a
> unique constraint in a table, but I cannot fully understand the difference,
> even though I have Google some articles about it. I will very much
> appreciate any guidance.
The SQL standard does not mention indexes anywhere. Therefore, in the
SQL standard world, the way to define uniqueness is by declaring an
unique constraint. Using unique constraints instead of unique indexes
means your code stays more portable. Unique constraints appear in
INFORMATION_SCHEMA.TABLE_CONSTRAINTS, whereas unique indexes do not.
PostgreSQL implements unique constraints by way of unique indexes (and
it's likely that all RDBMSs do likewise). Also, the syntax to declare
unique indexes allows for more features than the unique constraints
syntax. For example, you can have a unique index that covers only
portion of the table, based on a WHERE condition (a partial unique
index). You can't do this with a constraint.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alvherre@2ndquadrant.com, jorgemal1960@gmail.com
Subject: Re: Unique index and unique constraint
In-Reply-To: <20130726221937.GL14652@eldon.alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox