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/