Re: [SQL] Unique index VS unique constraint
So, let´s say that I have the following simple example table: 1. cus_id 2. cus_name 3. Other fields . . . Where cus_id is the primary key. And let´s also say that I want cus_name to be unique. I have the option to create a unique constraint or a unique index. What would be the best decision and why? Regards, Jorge Maldonado On Fri, Oct 4, 2013 at 5:38 PM, David Johnston pol...@yahoo.com wrote: JORGE MALDONADO wrote I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. A constraint says what valid data looks like. An index stores data in such a way as to enhance search performance. Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists. PostgreSQL has chosen to allow a user to create a unique index directly, instead of only via a constraint, but one should not do so. The uniqueness property is a constraint and so a unique index without a corresponding constraint is an improper model. If you look at the model without any indexes (which are non-model objects) you would not be aware of the fact that duplicates are not allowed yet in the implementation that is indeed the case. Logically the constraint layer sits on top of an index and performs its filtering of incoming data so that the index can focus on its roles of storing and retrieving. Extending this thought the underlying index should always be non-Unique and a unique filter/constraint would use that index for validation before passing the new value along. However, practicality leads to the current situation where the index takes on the added role of enforcing uniqueness. This is not the case for any other constraint but the UNIQUE constraints case is so integral to PRIMARY KEY usage that the special case behavior is understandable and much more performant. Conceptually the index is an implementation detail and uniqueness should be associated only with constraints. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
On Sat, Oct 5, 2013 at 3:24 PM, JORGE MALDONADO jorgemal1...@gmail.com wrote: So, let´s say that I have the following simple example table: 1. cus_id 2. cus_name 3. Other fields . . . Where cus_id is the primary key. And let´s also say that I want cus_name to be unique. I have the option to create a unique constraint or a unique index. What would be the best decision and why? Unique constraint would be the right choice, because you want to constrain the values in the column of your table. The unique index is just a tool the constrain uses to perform its function. This index will be created automatically when you add the constraint. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@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
[SQL] Unique index VS unique constraint
I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. Respectfully, Jorge Maldonado
Re: [SQL] Unique index VS unique constraint
Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. 2 main differences. First is the meaning: primary key identifies a record. A unique just tells you that that value of the record, in the table is unique. If you use keys, db structure will be more intelligible (my opinion). Second one is functional: in an unique constraint you can allow NULL values and ignore them. A primary key does not allow this. Respectfully, Jorge Maldonado Regards, Luca. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
On 10/04/2013 09:48 AM, JORGE MALDONADO wrote: I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. The way I think of it is, that since the SQL standard does not mention indices an INDEX (UNIQUE or otherwise) is just the databases application of a constraint. So for practical purposes they are the same thing. Respectfully, Jorge Maldonado -- Adrian Klaver adrian.kla...@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
Re: [SQL] Unique index VS unique constraint
On 10/04/2013 10:41 AM, luca...@gmail.com wrote: Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. 2 main differences. First is the meaning: primary key identifies a record. A unique just tells you that that value of the record, in the table is unique. If you use keys, db structure will be more intelligible (my opinion). Not sure I follow, you can have a unique index that is not a primary key. A primary key is special kind of unique index: http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows. Second one is functional: in an unique constraint you can allow NULL values and ignore them. A primary key does not allow this. Respectfully, Jorge Maldonado Regards, Luca. -- Adrian Klaver adrian.kla...@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
Re: [SQL] Unique index VS unique constraint
JORGE MALDONADO wrote I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. A constraint says what valid data looks like. An index stores data in such a way as to enhance search performance. Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists. PostgreSQL has chosen to allow a user to create a unique index directly, instead of only via a constraint, but one should not do so. The uniqueness property is a constraint and so a unique index without a corresponding constraint is an improper model. If you look at the model without any indexes (which are non-model objects) you would not be aware of the fact that duplicates are not allowed yet in the implementation that is indeed the case. Logically the constraint layer sits on top of an index and performs its filtering of incoming data so that the index can focus on its roles of storing and retrieving. Extending this thought the underlying index should always be non-Unique and a unique filter/constraint would use that index for validation before passing the new value along. However, practicality leads to the current situation where the index takes on the added role of enforcing uniqueness. This is not the case for any other constraint but the UNIQUE constraints case is so integral to PRIMARY KEY usage that the special case behavior is understandable and much more performant. Conceptually the index is an implementation detail and uniqueness should be associated only with constraints. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
Unique indexes can be partial, i.e. defined with a where clause (that must be included in a query so that PostgreSQL knows to use that index) whereas unique constraints cannot. JORGE MALDONADO wrote I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. A constraint says what valid data looks like. An index stores data in such a way as to enhance search performance. Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists. PostgreSQL has chosen to allow a user to create a unique index directly, instead of only via a constraint, but one should not do so. The uniqueness property is a constraint and so a unique index without a corresponding constraint is an improper model. If you look at the model without any indexes (which are non-model objects) you would not be aware of the fact that duplicates are not allowed yet in the implementation that is indeed the case. Logically the constraint layer sits on top of an index and performs its filtering of incoming data so that the index can focus on its roles of storing and retrieving. Extending this thought the underlying index should always be non-Unique and a unique filter/constraint would use that index for validation before passing the new value along. However, practicality leads to the current situation where the index takes on the added role of enforcing uniqueness. This is not the case for any other constraint but the UNIQUE constraints case is so integral to PRIMARY KEY usage that the special case behavior is understandable and much more performant. Conceptually the index is an implementation detail and uniqueness should be associated only with constraints. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
Steve Grey-2 wrote Unique indexes can be partial, i.e. defined with a where clause (that must be included in a query so that PostgreSQL knows to use that index) whereas unique constraints cannot. This implies there can be data in the table but not in the index and thus said index is not part of the model. This strikes me, though, as a shortcoming of the declarative constraint implementation since such behavior should not modeled via indexes even if that is how they are implemented. The where clause limitation on constraints is arbitrary though adding it just for this would not pass a cost-benefit analysis. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773434.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
