On Thu, 2003-05-15 at 10:07, Reece Hart wrote:
> I'd appreciate some advice regarding the use of foreign keys in
> inherited tables. Specifically, here's the schema I'd like:
Ok, you can't do it with native postgres RI stuff. I'm told that
the subject has been debated extensively, but right now it can't
be done.
I had to do the same thing as you and the way I've gone about it
is to write triggers in plpython that raise an exception if RI
is not maintained. I have a table that lists all the "x
references y" pairs, and it just reads that and does the query
to see if it can find a row in the "y" table with the right ID.
It was working quite well (within my limited testing) until I hit
a but in plpython last night. Said bug is on my todo list to take
a look at, but that won't be happening imediately.
You'll find you can't write this stuff easily in plpgsql, and I
don't recommend plpython right now because I don't trust it at
this point.
You might have some luck writing similar triggers in C. I'm
happy to send you my python triggers if you'd like something to
start from, but be warned: they're a bit of a mess.
HTH
James.