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/
