Re: Reference to multiple cols

Ville,

> I want to make a table with a column that references to multiple
> tables. Is that possible? Look at the 3rd row:
>
> 1. create table dog(barking_volume int,slobber_amount int);
> 2. create table cat(laziness int);
> 3. create table animals(name text,ref_animal oid references cat(oid)
> and dog(oid) );

First off, I reccommend against using the OID as your keying system.
The OID is used for specific system purposes, some of which may
interfere with using is as a primary and foriegn key. Use SERIAL
columns instead.

Second, the normal relational way to do the above would be:

create table animal( animal_id SERIAL PRIMARY KEY, name TEXT NOT NULL
);
create table dog( animal_id INT PRIMARY KEY REFERENCES animals
(animal_id), barking_volume INT, slobber INT );
create table cat( animal_id INT PRIMARY KEY REFERENCES animals
(animal_id), lazyness INT, shedding_amount INT );

This should give you a system in which animal_id is the primary key for
each table, and therefore there is a one-for-one relationship between
the animal table and each of the dog and cat tables, and would prevent
you from deleting a referenced record from the animal table.

You would need an additional trigger to prevent duplication *between*
the dog and cat tables.

-Josh Berkus