Re: Surrogate VS natural keys

Michael Glaesemann wrote:
>
> On Jun 20, 2007, at 11:28 , Joshua D. Drake wrote:
>
>> Richard Broersma Jr wrote:
>>
>>> I've often wondered about this. Since PostgreSQL allows FOREIGN KEYS
>>> to be referenced from UNIQUE
>>> (non-primary) natural keys, couldn't the schema be designed so that
>>> every table has a surrogate
>>> PRIMARY KEY and yet still maintain the relationships using UNIQUE
>>> natural keys.
>>> Would a design like this be practical?
>>
>> I would do it the other way. Have your primary keys be natural.
>
> The albeit small advantage of using PRIMARY KEY on your surrogate if
> you're using the surrogate for foreign key constraints is that you can
> leave off the column name when using REFERENCES: it'll default to the
> PRIMARY KEY column(s). For example:

Sure but for the sake of doing normalization correctly ;) a primary key
should be natural.

Joshua D. Drake

>
> CREATE TABLE foos
> (
> foo_id SERIAL PRIMARY KEY
> , foo TEXT NOT NULL
> , bal TEXT NOT NULL, UNIQUE (foo, bal)
> );
>
> CREATE TABLE bars
> (
> bar_id SERIAL PRIMARY KEY
> , bar TEXT NOT NULL
> , foo_id INTEGER NOT NULL
> REFERENCES foos
> );
>
> \d bars
> Table "public.bars"
> Column | Type | Modifiers
> --------+---------+-------------------------------------------------------
> bar_id | integer | not null default nextval('bars_bar_id_seq'::regclass)
> bar | text | not null
> foo_id | integer | not null
> Indexes:
> "bars_pkey" PRIMARY KEY, btree (bar_id)
> Foreign-key constraints:
> "bars_foo_id_fkey" FOREIGN KEY (foo_id) REFERENCES foos(foo_id)
>
> It does work for multi-column primary keys as well:
>
> CREATE TABLE baz_quuxen
> (
> baz TEXT NOT NULL
> , quux TEXT NOT NULL
> , PRIMARY KEY (baz, quux)
> );
>
> CREATE TABLE blurfls
> (
> blurfl TEXT PRIMARY KEY
> , baz TEXT NOT NULL
> , quux TEXT NOT NULL
> , FOREIGN KEY (baz, quux) REFERENCES baz_quuxen
> );
>
> \d blurfls
> Table "public.blurfls"
> Column | Type | Modifiers
> --------+------+-----------
> blurfl | text | not null
> baz | text | not null
> quux | text | not null
> Indexes:
> "blurfls_pkey" PRIMARY KEY, btree (blurfl)
> Foreign-key constraints:
> "blurfls_baz_fkey" FOREIGN KEY (baz, quux) REFERENCES
> baz_quuxen(baz, quux)
>
> Having PRIMARY KEY on your natural key does provide some additional
> documentation.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--

=== 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
PostgreSQL Replication: http://www.commandprompt.com/products/