[SQL] Unique index VS unique constraint

2013-10-04 Thread JORGE MALDONADO
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

2013-10-04 Thread luca...@gmail.com
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

Re: [SQL] Unique index VS unique constraint

2013-10-04 Thread Adrian Klaver
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

Re: [SQL] Unique index VS unique constraint

2013-10-04 Thread Adrian Klaver
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

[SQL] Advice on defining indexes

2013-10-04 Thread JORGE MALDONADO
I have a table with fields that I guess would be a good idea to set as indexes because users may query it to get results ordered by different criteria. For example: -- Artists Table -- 1. art_id 2. art_name 3. art_bday 4. art_sex 5. art_country (foreign key, there

Re: [SQL] Advice on defining indexes

2013-10-04 Thread David Johnston
JORGE MALDONADO wrote I have a table with fields that I guess would be a good idea to set as indexes because users may query it to get results ordered by different criteria. For example: -- Artists Table -- 1. art_id 2. art_name 3. art_bday 4. art_sex

Re: [SQL] Advice on defining indexes

2013-10-04 Thread JORGE MALDONADO
I really appreciate your fast and very complete answer. If a table has a foreign key on 2 fields, should I also create an index composed of such fields? For example: --- Table Sources --- 1. src_id 2. src_date 3. Other fields . . . Here, the primary key

Re: [SQL] Unique index VS unique constraint

2013-10-04 Thread David Johnston
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

Re: [SQL] Advice on defining indexes

2013-10-04 Thread David Johnston
JORGE MALDONADO wrote If a table has a foreign key on 2 fields, should I also create an index composed of such fields? Yes. If you want to truly/actually model a foreign key the system will require you to create a unique constraint/index on the primary/one side of the relationship. CREATE

Re: [SQL] Unique index VS unique constraint

2013-10-04 Thread Steve Grey
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

Re: [SQL] Unique index VS unique constraint

2013-10-04 Thread David Johnston
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