Re: Foreign keys?

On 13 Jul 2001, Dr. Evil 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:
>
> 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.

You need to define user.number as unique and add the constraint, something
like:

create table user (
 number int4 unique,
 name varchar(400)
);

create table object (
 owner int4 references user(number),
 description varchar(200)
)

However, there's different options based on what you want it to do in the
case you modify or delete rows out of user.