[SQL] how to inherits the references...

[frederik nietzsche]

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,
  kmtext,
  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(
  cartext   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:   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/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] how to inherits the references...

[frederik nietzsche]

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/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] about Inheritance...

[frederik nietzsche]

Hi all,
I'm still in trouble with inheritance.
Now I've created some triggers and rules for realize
something
similiar to an inheritance... I MUST do myself all the
check
for integrity, it seems the only way.
But because psql executes a function in a transaction
it was
not so bad...
In practice, if you insert some datas in a child
table, YOU must 
insert it also in the father table, resulting in
having two identical
data in the father table. if you don't do that, and
you insert
only in the child table, you can see the data in the
father,
but if you SELECT from it, seems it's not there...

I hope it is intelligible.
Any of you have ever dealt with such a thing??
I'd like to hear someother's solutions, if any.

I've also a question,
Is there any sql statement for identify, knowing it's
key value, 
where is (in which child table) a record in the father
table??
I have the key of a record in the father, and I want
to know in 
which child table is the real information


ok bye and thaks all...
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/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])