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
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
Re: [SQL] Unique index and unique constraint
2013/7/27 Alvaro Herrera alvhe...@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.
Re: [SQL] Unique index and unique constraint
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 jorgemal1...@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
Re: [SQL] Unique index and unique constraint
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 Herrerahttp://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
Re: [SQL] Unique index and unique constraint
On Fri, Jul 26, 2013 at 3:19 PM, Alvaro Herrera alvhe...@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...@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
