Thread: FOREIGN KEY questions

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

On Sun, Sep 10, 2000 at 12:43:08AM -0400, Neil Conway wrote:
> 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
>     /* ... */
> );

Something like

CREATE TABLE messages (
    /* ... */
    poster  INT4 NOT NULL REFERENCES users,
    thread  INT4 NOT NULL REFERENCES threads,
);

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

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

cf. Bruce's book:
  http://www.postgresql.org/docs/aw_pgsql_book/node156.html

Cheers,

Patrick

That is not a sin to have never used foreign keys, but I think you are
mixing apples and oranges. It sounds to me you have 2 questions. One is
how do I put data into tables and the other is how can I, using SQL
display a new table from colums you filled.

    The first question is answered I think. Sounds like you are
entering data properly, but you might want to record a key column with
every entry. This may not be possible if the data is being keyed on a web
page.

    Since your filling more than one table there must be a key column
in every table. Then an SQL "select from and" calls will work. Of course
if you can record date and time, that is a classic key. Tricky to use as I
discovered.

On Sun, 10 Sep 2000, Neil Conway wrote:

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

Yours Truly,

       - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -

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.