Re: Foreign keys?

The simple answer is as easy as:

CREATE TABLE user (
       number INT4 PRIMARY KEY,
       name VARCHAR(400)
);

CREATE TABLE object (
       owner INT4 REFERENCES user NOT NULL,
       description VARCHAR(200)
);

This will guarantee that object.owner will always be
one of the user.number values, and that you will not
be able to delete users that still have objects.  Both
of these actions will raise exceptions.

However, if you are going to do a lot of joins on your
user table along the lines of:

SELECT user.name, object.description FROM user, object
WHERE user.number = object.owner;

Then you might be better off simplifying just a bit to
give you something like:

CREATE TABLE user (
       name VARCHAR(400) PRIMARY KEY
);

CREATE TABLE object (
       owner VARCHAR(400) REFERENCES user NOT NULL,
       description VARCHAR(200)
);

That would save you having to join the table to find
the user.name at the expense of using more hard drive
space.

For more information see Bruce Momjian's excellent
book:

http://www.ca.postgresql.org/docs/aw_pgsql_book/node131.html

And if you like it consider buying a copy.  It's well
worth it.

Jason
--- "Dr. Evil" <drevil@sidereal.kz> wrote:
>
> There doesn't seem to be much documentation on
> foreign keys and how to
> use them properly.
>
> I am setting up a DB with two tables: users, and a
> table of objects
> where are always owned by users.  I want to make
> sure that there's no
> way to have an object which isn't owned.  Let's say
> these are the
> tables:
>
> CREATE TABLE user (
>        number INT4,
>        name VARCHAR(400)
> );
>
> and
>
> CREATE TABLE object (
>        owner INT4,
>        description VARCHAR(200)
> );
>
> The constraint is that there should never be a row
> in the object table
> where the owner column has a number which doesn't
> have a corresponding
> owner in the user table.
>
> I'm sure I can do something with foreign keys to
> implement this
> constraint, but I can't figure it out.
>
> Thanks
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/