Re: Reference to multiple cols

Thank You for help.

i have read that oid is not recommended only because it has restrictions(it
is int4). so it's still ok on small dbs?

i'll study how to use those triggers.

ville

>From: "Josh Berkus" <josh(at)agliodbs(dot)com>
>To: "Ville Jungman" <ville_jungman(at)hotmail(dot)com>,pgsql-novice(at)postgresql(dot)org
>Subject: Re: [NOVICE] Reference to multiple cols
>Date: Sat, 11 Jan 2003 14:00:14 -0800
>
>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail