Re: FOREIGN KEY questions

On Sun, 10 Sep 2000, Neil Conway wrote:

> 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 .

Well, I'd suggest:
CREATE TABLE users (
id serial PRIMARY KEY,
/* ... */
);

CREATE TABLE threads (
id serial PRIMARY KEY,
/* ... */
);

CREATE TABLE messages (
/* ... */
poster int4 NOT NULL CONSTRAINT fk_message_poster REFERENCES users,
thread int4 NOT NULL CONSTRAINT fk_message_thread REFERENCES threads
);

> 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).

Yeah, something like the above should work. It'll also prevent you
from removing a user who has mesages or threads that have messages.
You can make it do other things in those cases, like delete the
messages that are associated with that user or thread by
adding (ON DELETE CASCADE) to each of the constraints.