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

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 constrain

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 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.

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] 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.

[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 i

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

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, t

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. Fi

[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