Thread: Reference to multiple cols
Hi! 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) ); --- I want to use references to ensure that i can not delete a row from dog or cat tables if it is referenced from animals-table. --- Also I could reference straight to the general oid-value but I think it isn't possible either - like: create table animals(name text,ref_animal oid references <alltables>.oid); --- i and my cat Naukki (laziness == 98) will be glad for any help. ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) _________________________________________________________________ MSN 8: advanced junk mail protection and 2 months FREE*. http://join.msn.com/?page=features/junkmail
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
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@agliodbs.com> >To: "Ville Jungman" <ville_jungman@hotmail.com>,pgsql-novice@postgresql.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@postgresql.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
On Sat, Jan 11, 2003 at 14:00:14 -0800, Josh Berkus <josh@agliodbs.com> wrote: > > 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. There is a way to do this without triggers. You add an animal type field to all of the tables. The foreign keys then should use both animal id and animal type (which means you need to make animal_id and animal_type a combined unique key on the animal table). You add a constraint to the animal specific tables forcing the animal type to be the appropiate type.
ERROR: NEW used in non-rule query
OK, the above is the error I'm getting upon insert into my table
"prospectNotes". I've included the table schema and trigger/function
declarations. I have no idea why this is happening. I haven't had problems
like this with any of the other tables I have created with other trigger
procedures. I'm sure I have a syntactical or logical error in
v_i_prospectNotes but I sure can't see it. I'd be grateful if someone with
fresh eyes could take a look.
Thanks
Rod
CREATE table "prospectNotes"(
"prospectNoteID" serial NOT NULL CONSTRAINT "PK_prospectNotes1" PRIMARY KEY,
"note" text NOT NULL,
"noteAdded" timestamp DEFAULT now() NOT NULL,
"addedByID" int8 NOT NULL,
"prospectID" int8 NOT NULL,
CONSTRAINT "FK_prospectNotes_1" FOREIGN KEY ("prospectID") REFERENCES
"users" ("userID"),
CONSTRAINT "FK_prospectNotes_2" FOREIGN KEY ("addedByID") REFERENCES "users"
("userID"));
COMMENT ON COLUMN "prospectNotes"."addedByID" IS 'userid of author of this
note';
COMMENT ON COLUMN "prospectNotes"."prospectID" IS 'userid of prospect this
note is about';
create or replace function "v_i_prospectNotes"() returns opaque as '
declare
errors text := '''';
checkRec RECORD;
begin
if NEW."prospectNoteID" IS NULL then
errors = errors || ''\\nprospectNoteID\\tmust not be empty.'';
end if;
if NEW."note" IS NULL then
errors = errors || ''\\nnote\\tmust not be empty.'';
end if;
if NEW."addedByID" IS NULL then
errors = errors || ''\\naddedByID\\tmust not be empty.'';
else
select into checkRec count(*) as c from "users" where
"userID"=NEW."addedByID";
if checkRec.c=0 then
errors = errors || ''\\naddedByID\\tentered does not exist.'';
end if;
end if;
if NEW."prospectID" IS NULL then
errors = errors || ''\\nprospectID\\tmust not be empty.'';
else
select into checkRec count(*) as c from "users" where
"userID"=NEW."prospectID";
if checkRec.c=0 then
errors = errors || ''\\nprospectID\\tentered does not exist.'';
end if;
end if;
if errors != '''' then
raise exception ''%'',errors;
end if;
return NEW;
end;
' language 'plpgsql';
create or replace function "v_u_prospectNotes"() returns opaque as '
declare
errors text := '''';
checkRec RECORD;
begin
raise exception ''You may not edit notes!'';
end;
' language 'plpgsql';
create trigger "t_v_i_prospectNotes" before insert on "prospectNotes" for
each row execute procedure "v_i_prospectNotes"();
create trigger "t_v_u_prospectNotes" before update on "prospectNotes" for
each row execute procedure "v_u_prospectNotes"();
"Rod Kreisler" <rod@23net.net> writes:
> ERROR: NEW used in non-rule query
I tried to reproduce your problem in CVS tip, and saw no such error.
How old is your Postgres?
regards, tom lane
To answer your question 7.2.1... but I've found the problem. A string being inserted to the "note" field was not properly quoted and had the word "New" in it. > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane > Sent: Sunday, January 12, 2003 12:19 AM > To: Rod Kreisler > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] ERROR: NEW used in non-rule query > > > "Rod Kreisler" <rod@23net.net> writes: > > ERROR: NEW used in non-rule query > > I tried to reproduce your problem in CVS tip, and saw no such error. > How old is your Postgres? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Thank You for help! Still i have one problem left. How can i determine which table's row references to the main table row if i don't want to read every referencing table and search the right row. That was a part of the original problem and idea why i first wanted that the main table had the column that references to other tables. ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) >From: Bruno Wolff III <bruno@wolff.to> >To: Josh Berkus <josh@agliodbs.com> >CC: Ville Jungman <ville_jungman@hotmail.com>,pgsql-novice@postgresql.org >Subject: Re: [NOVICE] Reference to multiple cols >Date: Sat, 11 Jan 2003 18:51:21 -0600 > >On Sat, Jan 11, 2003 at 14:00:14 -0800, > Josh Berkus <josh@agliodbs.com> wrote: > > > > 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. > >There is a way to do this without triggers. You add an animal type >field to all of the tables. The foreign keys then should use both animal id >and animal type (which means you need to make animal_id and animal_type >a combined unique key on the animal table). You add a constraint to the >animal >specific tables forcing the animal type to be the appropiate type. > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
On Sun, Jan 12, 2003 at 15:06:03 +0200, Ville Jungman <ville_jungman@hotmail.com> wrote: > Thank You for help! > > Still i have one problem left. How can i determine which table's row > references to the main table row if i don't want to read every referencing > table and search the right row. That was a part of the original problem and > idea why i first wanted that the main table had the column that references > to other tables. If you use the suggestion to also have an animal type column in each table, then you can check the animal type to see which auxillary table to look at.
Relational databases are not particularly suited for building
heterogeneous hierarchies. You may try looking at some of the object
oriented databases which are perfect for this kind of relationship.
Having said that, you might also be satisfied with simply using the
PostgreSQL Inheritance feature. Personally, I have not yet used this on
production software but I have played with it enough to know that it
works *g*
The inheritance concept follows that you create a parent table (animal)
and then all of its children will inherit from this table. Thus, when
you do a select on the animal table, it will return all of its children,
even if they are heterogeneous.
Relevant DDL:
1 REATE TABLE animal (
2 name text NOT NULL
3 );
4
5 CREATE TABLE dog (
6 barking_volume integer,
7 slobber integer
8 )
9 INHERITS (animal);
10
11 CREATE TABLE cat (
12 lazyness integer,
13 shedding_amount integer
14 )
15 INHERITS (animal);
There are two things to nice, first is that I no longer need a serial id
(if your tables interacts with other parts of the system you might want
the ID back in there though ;-), the second thing to notice is the
INHERITS declaration (lines 9 & 15) which link the two tables to the
animal table via inheritance and cause them to acquire the name column.
Relevant queries:
lex=# INSERT INTO dog VALUES ('ralf', 2, 5);
INSERT 17113 1
lex=# INSERT INTO cat VALUES ('penelope', 9, 2);
INSERT 17114 1
lex=# SELECT * FROM animal;
name
----------
ralf
penelope
(2 rows)
lex=# SELECT * FROM dog;
name | barking_volume | slobber
------+----------------+---------
ralf | 2 | 5
(1 row)
lex=# SELECT * FROM cat;
name | lazyness | shedding_amount
----------+----------+-----------------
penelope | 9 | 2
(1 row)
lex=# DELETE FROM animal;
DELETE 2
Subtle points:
- Inserting into dog and cat makes the record available in animal.
- Deleting from animal also deletes the record from child tables.
- Child columns are not available to the parent (does not inherit up).
- Parent columns are part of the child (inherits down).
Unfortunately, there seems to be no way to find the child table name of
a row retrieved from the parent table. But I played with this several
months ago and don't remember all the gory details, I will see if I can
dig up some of my queries.
If you like the approach I described above then I would encourage you to
first learn about it as much as possible before implementing it. The
reason is that it's not standard amongst RDBMSs and so there is little
experience with this kind of setup.
happy hacking,
- lex
On Sun, 2003-01-12 at 08:06, Ville Jungman wrote:
> Thank You for help!
>
> Still i have one problem left. How can i determine which table's row
> references to the main table row if i don't want to read every referencing
> table and search the right row. That was a part of the original problem and
> idea why i first wanted that the main table had the column that references
> to other tables.
>
> ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland
> tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak
> usko Herraan Jeesukseen, niin sinä pelastut. (apt. 16:31)
>
>
> >From: Bruno Wolff III <bruno@wolff.to>
> >To: Josh Berkus <josh@agliodbs.com>
> >CC: Ville Jungman <ville_jungman@hotmail.com>,pgsql-novice@postgresql.org
> >Subject: Re: [NOVICE] Reference to multiple cols
> >Date: Sat, 11 Jan 2003 18:51:21 -0600
> >
> >On Sat, Jan 11, 2003 at 14:00:14 -0800,
> > Josh Berkus <josh@agliodbs.com> wrote:
> > >
> > > 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.
> >
> >There is a way to do this without triggers. You add an animal type
> >field to all of the tables. The foreign keys then should use both animal id
> >and animal type (which means you need to make animal_id and animal_type
> >a combined unique key on the animal table). You add a constraint to the
> >animal
> >specific tables forcing the animal type to be the appropiate type.
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to majordomo@postgresql.org so that your
> >message can get through to the mailing list cleanly
>
>
> _________________________________________________________________
> Protect your PC - get McAfee.com VirusScan Online
> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Lex Berezhny <LBerezhny@DevIS.com>
This seems to be exactly what i needed at first glance. --- >- Child columns are not available to the parent (does not inherit up). But I can read a child columns using oid (ok, i'll maybe add that serial id column) value that i've read from the parent table ...but still don't know which table. (I managed to get the needed values from child tables but by inefficient ways.) --- Bruno Wolff III wrote: >>If you use the suggestion to also have an animal type column in each >>table, >>then you can check the animal type to see which auxillary table to >>look at. Now i need only one column in animal table to do this. This is quite good way. I'll maybe use it by now, until the way to determine the child table name (or something other successful way) is found. --- Inheritances might be useful to many other purposes, too. Thank You*2 much*x! ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) >From: Lex Berezhny <LBerezhny@DevIS.com> >To: Ville Jungman <ville_jungman@hotmail.com> >CC: pgsql-novice@postgresql.org,"Martin N. Hudson" ><MHudson@DevIS.com>,Cristina Mossi Rhein <CMossiRhein@DevIS.com> >Subject: Re: [NOVICE] Reference to multiple cols >Date: 12 Jan 2003 12:14:51 -0500 > >Relational databases are not particularly suited for building >heterogeneous hierarchies. You may try looking at some of the object >oriented databases which are perfect for this kind of relationship. > >Having said that, you might also be satisfied with simply using the >PostgreSQL Inheritance feature. Personally, I have not yet used this on >production software but I have played with it enough to know that it >works *g* > >The inheritance concept follows that you create a parent table (animal) >and then all of its children will inherit from this table. Thus, when >you do a select on the animal table, it will return all of its children, >even if they are heterogeneous. > >Relevant DDL: > > 1 REATE TABLE animal ( > 2 name text NOT NULL > 3 ); > 4 > 5 CREATE TABLE dog ( > 6 barking_volume integer, > 7 slobber integer > 8 ) > 9 INHERITS (animal); > 10 > 11 CREATE TABLE cat ( > 12 lazyness integer, > 13 shedding_amount integer > 14 ) > 15 INHERITS (animal); > >There are two things to nice, first is that I no longer need a serial id >(if your tables interacts with other parts of the system you might want >the ID back in there though ;-), the second thing to notice is the >INHERITS declaration (lines 9 & 15) which link the two tables to the >animal table via inheritance and cause them to acquire the name column. > >Relevant queries: > >lex=# INSERT INTO dog VALUES ('ralf', 2, 5); >INSERT 17113 1 >lex=# INSERT INTO cat VALUES ('penelope', 9, 2); >INSERT 17114 1 >lex=# SELECT * FROM animal; > name >---------- > ralf > penelope >(2 rows) >lex=# SELECT * FROM dog; > name | barking_volume | slobber >------+----------------+--------- > ralf | 2 | 5 >(1 row) >lex=# SELECT * FROM cat; > name | lazyness | shedding_amount >----------+----------+----------------- > penelope | 9 | 2 >(1 row) >lex=# DELETE FROM animal; >DELETE 2 > >Subtle points: > > - Inserting into dog and cat makes the record available in animal. > - Deleting from animal also deletes the record from child tables. > - Child columns are not available to the parent (does not inherit up). > - Parent columns are part of the child (inherits down). > >Unfortunately, there seems to be no way to find the child table name of >a row retrieved from the parent table. But I played with this several >months ago and don't remember all the gory details, I will see if I can >dig up some of my queries. > >If you like the approach I described above then I would encourage you to >first learn about it as much as possible before implementing it. The >reason is that it's not standard amongst RDBMSs and so there is little >experience with this kind of setup. > >happy hacking, > > - lex > >On Sun, 2003-01-12 at 08:06, Ville Jungman wrote: > > Thank You for help! > > > > Still i have one problem left. How can i determine which table's row > > references to the main table row if i don't want to read every >referencing > > table and search the right row. That was a part of the original problem >and > > idea why i first wanted that the main table had the column that >references > > to other tables. > > > > ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland > > tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak > > usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) > > > > > > >From: Bruno Wolff III <bruno@wolff.to> > > >To: Josh Berkus <josh@agliodbs.com> > > >CC: Ville Jungman ><ville_jungman@hotmail.com>,pgsql-novice@postgresql.org > > >Subject: Re: [NOVICE] Reference to multiple cols > > >Date: Sat, 11 Jan 2003 18:51:21 -0600 > > > > > >On Sat, Jan 11, 2003 at 14:00:14 -0800, > > > Josh Berkus <josh@agliodbs.com> wrote: > > > > > > > > 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. > > > > > >There is a way to do this without triggers. You add an animal type > > >field to all of the tables. The foreign keys then should use both >animal id > > >and animal type (which means you need to make animal_id and animal_type > > >a combined unique key on the animal table). You add a constraint to the > > >animal > > >specific tables forcing the animal type to be the appropiate type. > > > > > >---------------------------(end of >broadcast)--------------------------- > > >TIP 3: if posting/reading through Usenet, please send an appropriate > > >subscribe-nomail command to majordomo@postgresql.org so that your > > >message can get through to the mailing list cleanly > > > > > > _________________________________________________________________ > > Protect your PC - get McAfee.com VirusScan Online > > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org >-- >Lex Berezhny <LBerezhny@DevIS.com> > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org _________________________________________________________________ The new MSN 8 is here: Try it free* for 2 months http://join.msn.com/?page=dept/dialup
After looking through the inheritance chapter in the PostgreSQL docs (http://www.zill.net/pgdocs/inherit.html) I found the solution: lex=# SELECT name, relname FROM animal, pg_class WHERE animal.tableoid = pg_class.oid; name | relname ----------+--------- ralf | dog penelope | cat (2 rows) good luck, - lex On Sun, 2003-01-12 at 19:00, Ville Jungman wrote: > This seems to be exactly what i needed at first glance. > > --- > > >- Child columns are not available to the parent (does not inherit up). > > But I can read a child columns using oid (ok, i'll maybe add that serial id > column) value that i've read from the parent table ...but still don't know > which table. (I managed to get the needed values from child tables but by > inefficient ways.) > > --- > > Bruno Wolff III wrote: > >>If you use the suggestion to also have an animal type column in each > >>table, > >>then you can check the animal type to see which auxillary table to > >>look at. > > Now i need only one column in animal table to do this. This is quite good > way. I'll maybe use it by now, until the way to determine the child table > name (or something other successful way) is found. > > --- > > Inheritances might be useful to many other purposes, too. Thank You*2 > much*x! > > ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland > tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak > usko Herraan Jeesukseen, niin sinä pelastut. (apt. 16:31) > > > > > >From: Lex Berezhny <LBerezhny@DevIS.com> > >To: Ville Jungman <ville_jungman@hotmail.com> > >CC: pgsql-novice@postgresql.org,"Martin N. Hudson" > ><MHudson@DevIS.com>,Cristina Mossi Rhein <CMossiRhein@DevIS.com> > >Subject: Re: [NOVICE] Reference to multiple cols > >Date: 12 Jan 2003 12:14:51 -0500 > > > >Relational databases are not particularly suited for building > >heterogeneous hierarchies. You may try looking at some of the object > >oriented databases which are perfect for this kind of relationship. > > > >Having said that, you might also be satisfied with simply using the > >PostgreSQL Inheritance feature. Personally, I have not yet used this on > >production software but I have played with it enough to know that it > >works *g* > > > >The inheritance concept follows that you create a parent table (animal) > >and then all of its children will inherit from this table. Thus, when > >you do a select on the animal table, it will return all of its children, > >even if they are heterogeneous. > > > >Relevant DDL: > > > > 1 REATE TABLE animal ( > > 2 name text NOT NULL > > 3 ); > > 4 > > 5 CREATE TABLE dog ( > > 6 barking_volume integer, > > 7 slobber integer > > 8 ) > > 9 INHERITS (animal); > > 10 > > 11 CREATE TABLE cat ( > > 12 lazyness integer, > > 13 shedding_amount integer > > 14 ) > > 15 INHERITS (animal); > > > >There are two things to nice, first is that I no longer need a serial id > >(if your tables interacts with other parts of the system you might want > >the ID back in there though ;-), the second thing to notice is the > >INHERITS declaration (lines 9 & 15) which link the two tables to the > >animal table via inheritance and cause them to acquire the name column. > > > >Relevant queries: > > > >lex=# INSERT INTO dog VALUES ('ralf', 2, 5); > >INSERT 17113 1 > >lex=# INSERT INTO cat VALUES ('penelope', 9, 2); > >INSERT 17114 1 > >lex=# SELECT * FROM animal; > > name > >---------- > > ralf > > penelope > >(2 rows) > >lex=# SELECT * FROM dog; > > name | barking_volume | slobber > >------+----------------+--------- > > ralf | 2 | 5 > >(1 row) > >lex=# SELECT * FROM cat; > > name | lazyness | shedding_amount > >----------+----------+----------------- > > penelope | 9 | 2 > >(1 row) > >lex=# DELETE FROM animal; > >DELETE 2 > > > >Subtle points: > > > > - Inserting into dog and cat makes the record available in animal. > > - Deleting from animal also deletes the record from child tables. > > - Child columns are not available to the parent (does not inherit up). > > - Parent columns are part of the child (inherits down). > > > >Unfortunately, there seems to be no way to find the child table name of > >a row retrieved from the parent table. But I played with this several > >months ago and don't remember all the gory details, I will see if I can > >dig up some of my queries. > > > >If you like the approach I described above then I would encourage you to > >first learn about it as much as possible before implementing it. The > >reason is that it's not standard amongst RDBMSs and so there is little > >experience with this kind of setup. > > > >happy hacking, > > > > - lex > > > >On Sun, 2003-01-12 at 08:06, Ville Jungman wrote: > > > Thank You for help! > > > > > > Still i have one problem left. How can i determine which table's row > > > references to the main table row if i don't want to read every > >referencing > > > table and search the right row. That was a part of the original problem > >and > > > idea why i first wanted that the main table had the column that > >references > > > to other tables. > > > > > > ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland > > > tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak > > > usko Herraan Jeesukseen, niin sinä pelastut. (apt. 16:31) > > > > > > > > > >From: Bruno Wolff III <bruno@wolff.to> > > > >To: Josh Berkus <josh@agliodbs.com> > > > >CC: Ville Jungman > ><ville_jungman@hotmail.com>,pgsql-novice@postgresql.org > > > >Subject: Re: [NOVICE] Reference to multiple cols > > > >Date: Sat, 11 Jan 2003 18:51:21 -0600 > > > > > > > >On Sat, Jan 11, 2003 at 14:00:14 -0800, > > > > Josh Berkus <josh@agliodbs.com> wrote: > > > > > > > > > > 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. > > > > > > > >There is a way to do this without triggers. You add an animal type > > > >field to all of the tables. The foreign keys then should use both > >animal id > > > >and animal type (which means you need to make animal_id and animal_type > > > >a combined unique key on the animal table). You add a constraint to the > > > >animal > > > >specific tables forcing the animal type to be the appropiate type. > > > > > > > >---------------------------(end of > >broadcast)--------------------------- > > > >TIP 3: if posting/reading through Usenet, please send an appropriate > > > >subscribe-nomail command to majordomo@postgresql.org so that your > > > >message can get through to the mailing list cleanly > > > > > > > > > _________________________________________________________________ > > > Protect your PC - get McAfee.com VirusScan Online > > > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > >-- > >Lex Berezhny <LBerezhny@DevIS.com> > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > _________________________________________________________________ > The new MSN 8 is here: Try it free* for 2 months > http://join.msn.com/?page=dept/dialup -- Lex Berezhny <LBerezhny@DevIS.com>
This is it! Thank You very much, Lex. Case closed :-) ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) >From: Lex Berezhny <LBerezhny@DevIS.com> >To: Ville Jungman <ville_jungman@hotmail.com> >CC: pgsql-novice@postgresql.org >Subject: Re: [NOVICE] Reference to multiple cols >Date: 12 Jan 2003 19:52:29 -0500 > >After looking through the inheritance chapter in the PostgreSQL docs >(http://www.zill.net/pgdocs/inherit.html) I found the solution: > >lex=# SELECT name, relname FROM animal, pg_class WHERE animal.tableoid = >pg_class.oid; > name | relname >----------+--------- > ralf | dog > penelope | cat >(2 rows) > > >good luck, > - lex > >On Sun, 2003-01-12 at 19:00, Ville Jungman wrote: > > This seems to be exactly what i needed at first glance. > > > > --- > > > > >- Child columns are not available to the parent (does not inherit up). > > > > But I can read a child columns using oid (ok, i'll maybe add that serial >id > > column) value that i've read from the parent table ...but still don't >know > > which table. (I managed to get the needed values from child tables but >by > > inefficient ways.) > > > > --- > > > > Bruno Wolff III wrote: > > >>If you use the suggestion to also have an animal type column in each > > >>table, > > >>then you can check the animal type to see which auxillary table to > > >>look at. > > > > Now i need only one column in animal table to do this. This is quite >good > > way. I'll maybe use it by now, until the way to determine the child >table > > name (or something other successful way) is found. > > > > --- > > > > Inheritances might be useful to many other purposes, too. Thank You*2 > > much*x! > > > > ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland > > tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak > > usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) > > > > > > > > > > >From: Lex Berezhny <LBerezhny@DevIS.com> > > >To: Ville Jungman <ville_jungman@hotmail.com> > > >CC: pgsql-novice@postgresql.org,"Martin N. Hudson" > > ><MHudson@DevIS.com>,Cristina Mossi Rhein <CMossiRhein@DevIS.com> > > >Subject: Re: [NOVICE] Reference to multiple cols > > >Date: 12 Jan 2003 12:14:51 -0500 > > > > > >Relational databases are not particularly suited for building > > >heterogeneous hierarchies. You may try looking at some of the object > > >oriented databases which are perfect for this kind of relationship. > > > > > >Having said that, you might also be satisfied with simply using the > > >PostgreSQL Inheritance feature. Personally, I have not yet used this on > > >production software but I have played with it enough to know that it > > >works *g* > > > > > >The inheritance concept follows that you create a parent table (animal) > > >and then all of its children will inherit from this table. Thus, when > > >you do a select on the animal table, it will return all of its >children, > > >even if they are heterogeneous. > > > > > >Relevant DDL: > > > > > > 1 REATE TABLE animal ( > > > 2 name text NOT NULL > > > 3 ); > > > 4 > > > 5 CREATE TABLE dog ( > > > 6 barking_volume integer, > > > 7 slobber integer > > > 8 ) > > > 9 INHERITS (animal); > > > 10 > > > 11 CREATE TABLE cat ( > > > 12 lazyness integer, > > > 13 shedding_amount integer > > > 14 ) > > > 15 INHERITS (animal); > > > > > >There are two things to nice, first is that I no longer need a serial >id > > >(if your tables interacts with other parts of the system you might want > > >the ID back in there though ;-), the second thing to notice is the > > >INHERITS declaration (lines 9 & 15) which link the two tables to the > > >animal table via inheritance and cause them to acquire the name column. > > > > > >Relevant queries: > > > > > >lex=# INSERT INTO dog VALUES ('ralf', 2, 5); > > >INSERT 17113 1 > > >lex=# INSERT INTO cat VALUES ('penelope', 9, 2); > > >INSERT 17114 1 > > >lex=# SELECT * FROM animal; > > > name > > >---------- > > > ralf > > > penelope > > >(2 rows) > > >lex=# SELECT * FROM dog; > > > name | barking_volume | slobber > > >------+----------------+--------- > > > ralf | 2 | 5 > > >(1 row) > > >lex=# SELECT * FROM cat; > > > name | lazyness | shedding_amount > > >----------+----------+----------------- > > > penelope | 9 | 2 > > >(1 row) > > >lex=# DELETE FROM animal; > > >DELETE 2 > > > > > >Subtle points: > > > > > > - Inserting into dog and cat makes the record available in animal. > > > - Deleting from animal also deletes the record from child tables. > > > - Child columns are not available to the parent (does not inherit >up). > > > - Parent columns are part of the child (inherits down). > > > > > >Unfortunately, there seems to be no way to find the child table name of > > >a row retrieved from the parent table. But I played with this several > > >months ago and don't remember all the gory details, I will see if I can > > >dig up some of my queries. > > > > > >If you like the approach I described above then I would encourage you >to > > >first learn about it as much as possible before implementing it. The > > >reason is that it's not standard amongst RDBMSs and so there is little > > >experience with this kind of setup. > > > > > >happy hacking, > > > > > > - lex > > > > > >On Sun, 2003-01-12 at 08:06, Ville Jungman wrote: > > > > Thank You for help! > > > > > > > > Still i have one problem left. How can i determine which table's row > > > > references to the main table row if i don't want to read every > > >referencing > > > > table and search the right row. That was a part of the original >problem > > >and > > > > idea why i first wanted that the main table had the column that > > >references > > > > to other tables. > > > > > > > > ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland > > > > tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak > > > > usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) > > > > > > > > > > > > >From: Bruno Wolff III <bruno@wolff.to> > > > > >To: Josh Berkus <josh@agliodbs.com> > > > > >CC: Ville Jungman > > ><ville_jungman@hotmail.com>,pgsql-novice@postgresql.org > > > > >Subject: Re: [NOVICE] Reference to multiple cols > > > > >Date: Sat, 11 Jan 2003 18:51:21 -0600 > > > > > > > > > >On Sat, Jan 11, 2003 at 14:00:14 -0800, > > > > > Josh Berkus <josh@agliodbs.com> wrote: > > > > > > > > > > > > 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. > > > > > > > > > >There is a way to do this without triggers. You add an animal type > > > > >field to all of the tables. The foreign keys then should use both > > >animal id > > > > >and animal type (which means you need to make animal_id and >animal_type > > > > >a combined unique key on the animal table). You add a constraint to >the > > > > >animal > > > > >specific tables forcing the animal type to be the appropiate type. > > > > > > > > > >---------------------------(end of > > >broadcast)--------------------------- > > > > >TIP 3: if posting/reading through Usenet, please send an >appropriate > > > > >subscribe-nomail command to majordomo@postgresql.org so that your > > > > >message can get through to the mailing list cleanly > > > > > > > > > > > > _________________________________________________________________ > > > > Protect your PC - get McAfee.com VirusScan Online > > > > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 > > > > > > > > > > > > ---------------------------(end of >broadcast)--------------------------- > > > > TIP 6: Have you searched our list archives? > > > > > > > > http://archives.postgresql.org > > >-- > > >Lex Berezhny <LBerezhny@DevIS.com> > > > > > > > > >---------------------------(end of >broadcast)--------------------------- > > >TIP 1: subscribe and unsubscribe commands go to >majordomo@postgresql.org > > > > > > _________________________________________________________________ > > The new MSN 8 is here: Try it free* for 2 months > > http://join.msn.com/?page=dept/dialup >-- >Lex Berezhny <LBerezhny@DevIS.com> > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster _________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail