pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to inherits the references...
4+ messages / 2 participants
[nested] [flat]
* how to inherits the references...
@ 2002-07-11 14:43 frederik nietzsche <nietzsche_psql@yahoo.it>
2002-07-11 16:00 ` Re: how to inherits the references... Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: frederik nietzsche @ 2002-07-11 14:43 UTC (permalink / raw)
To: pgsql-sql
hi all,
I've created some table with the inharitance,
something like:
CREATE TABLE sigles(
sigle varchar(255) PRIMARY KEY
);
CREATE TABLE cars(
UNIQUE (sigle)
)INHERITS (sigles);
CREATE TABLE used_cars(
old_owner text,
km text,
model text,
year text,
PRIMARY KEY (sigle)
)INHERITS (cars);
CREATE TABLE new_cars(
model text,
type text,
some_other text,
PRIMARY KEY (sigle)
)INHERITS (cars);
then I have a table of owner (with some fields) that
does not inherits nothing.
and then I have a table for the relation between
used_cars and old_owner:
CREATE TABLE cars_owner_relations(
car text REFERENCES used_cars (sigle),
owner text REFERENCES old_owner (id)
);
now, when I insert used_cars it also create a sigle
inside the "sigles" table, and this is OK, but when I
insert a record inside the cars_owner_relations it
says:
ERROR: <unnamed> referential integrity violation -
key referenced from cars_owner_relations not found in
sigles
as if the sigles where not in the "sigles" table, but
it's there!
it's probably because of the way in which psql threats
the inheritance.
my question is (finally): is there some workaround for
this?? or: am I making some mistakes??
ok, thanks and sorry for my english...
danilo
______________________________________________________________________
Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità .
http://it.yahoo.com/mail_it/foot/?http://it.messenger.yahoo.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to inherits the references...
2002-07-11 14:43 how to inherits the references... frederik nietzsche <nietzsche_psql@yahoo.it>
@ 2002-07-11 16:00 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-12 10:29 ` Re: how to inherits the references... frederik nietzsche <nietzsche_psql@yahoo.it>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2002-07-11 16:00 UTC (permalink / raw)
To: frederik nietzsche <nietzsche_psql@yahoo.it>; +Cc: pgsql-sql
On Thu, 11 Jul 2002, [iso-8859-1] frederik nietzsche wrote:
> as if the sigles where not in the "sigles" table, but
> it's there!
> it's probably because of the way in which psql threats
> the inheritance.
> my question is (finally): is there some workaround for
> this?? or: am I making some mistakes??
Foreign keys don't inherit to children table on either
the fk or pk side. Note also that the primary keys in
the above will not guarantee that sigle is unique
across the whole set, only across each table individually.
Pretty much the only workaround I know of is to make a table
with the key columns and have each of the tables in the
inheritance tree have its key columns reference that and anything
that wants to reference the inheritance tree references
the other table instead.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to inherits the references...
2002-07-11 14:43 how to inherits the references... frederik nietzsche <nietzsche_psql@yahoo.it>
2002-07-11 16:00 ` Re: how to inherits the references... Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-07-12 10:29 ` frederik nietzsche <nietzsche_psql@yahoo.it>
2002-07-12 14:03 ` Re: how to inherits the references... Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: frederik nietzsche @ 2002-07-12 10:29 UTC (permalink / raw)
To: pgsql-sql
ok, thanks for the (double: two mails ;) ) help, but
in this way when I insert a record in a child table,
the key must be already present in the "sigles" table,
otherwise it breaks the reference and doesn't insert
anything.
In order to use this solution I must create a set of
function that when I want to insert something in a
child tables it automatically insert BEFORE, the sigle
in the "sigles" table and THEN insert the values in
the child table.
If this is the only way, I'm going to use it, but I'm
not really satisfied by it...
ciao
danilo
> Foreign keys don't inherit to children table on
> either
> the fk or pk side. Note also that the primary keys
> in
> the above will not guarantee that sigle is unique
> across the whole set, only across each table
> individually.
>
> Pretty much the only workaround I know of is to make
> a table
> with the key columns and have each of the tables in
> the
> inheritance tree have its key columns reference that
> and anything
> that wants to reference the inheritance tree
> references
> the other table instead.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
______________________________________________________________________
Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità .
http://it.yahoo.com/mail_it/foot/?http://it.messenger.yahoo.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to inherits the references...
2002-07-11 14:43 how to inherits the references... frederik nietzsche <nietzsche_psql@yahoo.it>
2002-07-11 16:00 ` Re: how to inherits the references... Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-12 10:29 ` Re: how to inherits the references... frederik nietzsche <nietzsche_psql@yahoo.it>
@ 2002-07-12 14:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-07-12 14:03 UTC (permalink / raw)
To: frederik nietzsche <nietzsche_psql@yahoo.it>; +Cc: pgsql-sql
On Fri, 12 Jul 2002, [iso-8859-1] frederik nietzsche wrote:
> ok, thanks for the (double: two mails ;) ) help, but
> in this way when I insert a record in a child table,
> the key must be already present in the "sigles" table,
> otherwise it breaks the reference and doesn't insert
> anything.
> In order to use this solution I must create a set of
> function that when I want to insert something in a
> child tables it automatically insert BEFORE, the sigle
> in the "sigles" table and THEN insert the values in
> the child table.
A before trigger to insert into the key table would
probably work. Another advantage to this sort of thing
is that you could probably actually guarantee uniqueness
of your key across the entire inheritance hierarchy
which you can't otherwise.
> If this is the only way, I'm going to use it, but I'm
> not really satisfied by it...
Inheritance is only marginally useful right now.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-12 14:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-11 14:43 how to inherits the references... frederik nietzsche <nietzsche_psql@yahoo.it>
2002-07-11 16:00 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-12 10:29 ` frederik nietzsche <nietzsche_psql@yahoo.it>
2002-07-12 14:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox