From: Kynn Jones <kynnjo@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Basic Q on superfluous primary keys
Date: Sat, 14 Apr 2007 07:19:30 -0400
Message-ID: <c2350ba40704140419r2f3c85dfi60171759b71f4db7@mail.gmail.com> (raw)
Consider these two very similar schemas:
Schema 1:
CREATE TABLE foo (
id serial PRIMARY KEY,
frobnitz character(varying 100) NOT NULL UNIQUE
);
CREATE TABLE bar (
id serial PRIMARY KEY,
foo_id int REFERENCES foo(id)
)
Schema 2:
CREATE TABLE foo (
frobnitz character(varying 100) PRIMARY KEY
);
CREATE TABLE bar (
id serial PRIMARY KEY,
frobnitz character(varying 100) REFERENCES foo(frobnitz)
)
The two situations are semantically identical: each record in table bar
refers to a record in table foo. The difference is that in the first
schema, this referencing is done through an "artificial" serial-integer
primary key, while in the second schema this reference is done through a
data field that happens to be unique and not null, so it can serve as
primary key.
I find Schema 1 awkward and unnatural; more specifically, foo.id seems
unnecessary in light of the non-null uniqueness of foo.frobnitz. But I
remember once reading that "long" fields like foo.frobnitz did not make good
primary keys.
Is the field foo.id in Schema 1 superfluous? For example, wouldn't the
referencing from bar to foo really be done "behind the scenes" through some
hidden field (oid?) instead of through the frobnitz text field? Which of
the two schemas would give better perfornance?
Thanks!
kj
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kynnjo@gmail.com
Subject: Re: Basic Q on superfluous primary keys
In-Reply-To: <c2350ba40704140419r2f3c85dfi60171759b71f4db7@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox