pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnique index and unique constraint
5+ messages / 5 participants
[nested] [flat]
* Unique index and unique constraint
@ 2013-07-26 21:44 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: JORGE MALDONADO @ 2013-07-26 21:44 UTC (permalink / raw)
To: pgsql-sql
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.
Respectfully,
Jorge Maldonado
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unique index and unique constraint
@ 2013-07-26 22:07 Luca Vernini <lucazeo@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Luca Vernini @ 2013-07-26 22:07 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
I try to explain my point of view, also in my not so good English:
A primary key is defined by dr. Codd in relational model.
The key is used to identify a record. In good practice, you must always
define a primary key. Always.
The unique constraint will simply say: this value (or combination) should
not be found more than one time on this column in this table.
So you can say: just a convention?
Consider this:
If you say unique, you can still accept multiple rows with the same NULL
value. This is not true with primary key.
You can define multiple unique constraint on a table, but only a primary
key. This, and the concept of primary key, can help someone else to read
your database. To know in same cases, the logic of the data, and know what
identifies a row. That is not simply the same as: not duplicate this value.
Luca.
2013/7/26 JORGE MALDONADO <jorgemal1960@gmail.com>
> 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.
>
> Respectfully,
> Jorge Maldonado
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unique index and unique constraint
@ 2013-07-26 22:19 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 2 replies; 5+ messages in thread
From: Alvaro Herrera @ 2013-07-26 22:19 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unique index and unique constraint
@ 2013-07-27 01:56 Sergey Konoplev <gray.ru@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 5+ messages in thread
From: Sergey Konoplev @ 2013-07-27 01:56 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
On Fri, Jul 26, 2013 at 3:19 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> 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.
Also, AFAIU, one can defer the uniqueness check until the end of
transaction if it is constraint, and can not it it is unique index.
Correct?
http://www.postgresql.org/docs/9.2/static/sql-set-constraints.html
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unique index and unique constraint
@ 2013-07-27 07:13 Dmitriy Igrishin <dmitigr@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dmitriy Igrishin @ 2013-07-27 07:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
2013/7/27 Alvaro Herrera <alvherre@2ndquadrant.com>
>
> 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.
>
Note, partial uniqueness can be achieved by using EXCLUDE contraints also.
--
// Dmitriy.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-07-27 07:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-26 21:44 Unique index and unique constraint JORGE MALDONADO <jorgemal1960@gmail.com>
2013-07-26 22:07 ` Luca Vernini <lucazeo@gmail.com>
2013-07-26 22:19 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2013-07-27 01:56 ` Sergey Konoplev <gray.ru@gmail.com>
2013-07-27 07:13 ` Dmitriy Igrishin <dmitigr@gmail.com>
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