Re: Surrogate VS natural keys

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:

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