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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
