I'm having a bit of difficulty understanding the correct usage of
foreign keys (I've never used them before, excuse my ignorance).
Here's a situation where I'm trying to figure out how/if they
should be used:
One database has a group of tables with intereferential data.
For example:
CREATE TABLE messages (
/* ... */
poster INT4 NOT NULL,
thread INT4 NOT NULL
);
CREATE TABLE users (
id serial
/* ... */
);
CREATE TABLE threads (
id serial
/* ... */
);
messages.poster should refer to a valid (and unique) users.id - that
is the person who posted the message. If there is no users.id with
the same value as every messages.poster , there is a problem. The
same applies to message.thread and threads.id .
How do I check that this data is being entered correctly? Is this
a situation where I should be using foreign keys? Or can this only
be done with external logic (i.e. the application doing the inserts
should check first that the data it's receiving is valid).
Thanks in advance,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
We hang the petty thieves and appoint the great ones to public office.
-- Aesop
