Re: indexes

On Fri, 2006-11-24 at 09:54 -0500, Tom Allison wrote:
> I notice a lot of places where people use the approach of creating an index and
> a unique key like:
>
> CREATE TABLE foo (
> idx SERIAL PRIMARY KEY,
> name varchar(32) UNIQUE NOT NULL
> )
>
> instead of
> CREATE TABLE foo (
> name varchar(32) PRIMARY KEY
> )
>
> If the name is NEVER going to change, is there any advantage to doing this?
> If there are many-to-many reference tables (like name-to-friends) is this any
> different?

THe point of the first table is to have a artificial key that allows
easy access to the row.

It is easier to say: select * from foo where id = 5;

>
> I've seen this a lot, but I've always assumed that with the condition that
> 'name' would NEVER change, there was no advantage.

Technically, it also violates normal form as your primary key should be
on data that is representative. Although this:

CREATE TABLE users ( id SERIAL PRIMARY KEY,

name varchar(32) UNIQUE NOT NULL
)

Would make more sense because id is representative of the users.id which
is representative from an application stand point.

Sincerely,

Joshua D. Drake

>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate