Inheritance and foreign keys
Hi all,
I read on the manual page for Inheritance that:
"A limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children. Thus, in the above example,
specifying that another table's column REFERENCES cities(name) would
allow the other table to contain city names but not capital names. This
deficiency will probably be fixed in some future release."
I have a few projects that could benefit from inherited table structure,
and it's a very cool idea, but this inability of indexes to include
derived tables is a real functionality-killer. It's not "Object
Relational" if the objects can't be related to anything!
If someone could give me an idea of how far away this fix is, I'd be
grateful.
Cheers
BJ
Brendan Jurd wrote:
I have a few projects that could benefit from inherited table
structure, and it's a very cool idea, but this inability of indexes
to include derived tables is a real functionality-killer. It's not
"Object Relational" if the objects can't be related to anything!
Hehe, but the "relational" means that the data is stored in relations,
not that the data has to be related to other data.
If someone could give me an idea of how far away this fix is, I'd be
grateful.
No one has really expressed intentions to fix this, so I wouldn't hold
my breath.
On Tue, 9 Dec 2003, Brendan Jurd wrote:
Hi all,
I read on the manual page for Inheritance that:
"A limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children. Thus, in the above example,
specifying that another table's column REFERENCES cities(name) would
allow the other table to contain city names but not capital names. This
deficiency will probably be fixed in some future release."I have a few projects that could benefit from inherited table structure,
and it's a very cool idea, but this inability of indexes to include
derived tables is a real functionality-killer. It's not "Object
Relational" if the objects can't be related to anything!If someone could give me an idea of how far away this fix is, I'd be
grateful.
I'd say at least 1, probably more versions out. Unique constraints across
an inheritance tree are pretty much a requirement for inherited foreign
keys and I'm not planning to even start thinking about the foreign key
side until after unique's done.
As a note, there are workarounds for foreign keys that have been mentioned
in the past. I'm not really sure anyone's post a real good workaround for
unique though.
On Mon, 8 Dec 2003, Stephan Szabo wrote:
On Tue, 9 Dec 2003, Brendan Jurd wrote:
Hi all,
I read on the manual page for Inheritance that:
"A limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children. Thus, in the above example,
specifying that another table's column REFERENCES cities(name) would
allow the other table to contain city names but not capital names. This
deficiency will probably be fixed in some future release."I have a few projects that could benefit from inherited table structure,
and it's a very cool idea, but this inability of indexes to include
derived tables is a real functionality-killer. It's not "Object
Relational" if the objects can't be related to anything!If someone could give me an idea of how far away this fix is, I'd be
grateful.I'd say at least 1, probably more versions out. Unique constraints across
Errm, that was supposed to say "at least 2," given the absense of a good
plan.
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
Peter Eisentraut wrote:<br>
<blockquote cite="mid200312082138.54363.peter_e@gmx.net" type="cite">
<pre wrap="">Brendan Jurd wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I have a few projects that could benefit from inherited table
structure, and it's a very cool idea, but this inability of indexes
to include derived tables is a real functionality-killer. It's not
"Object Relational" if the objects can't be related to anything!
</pre>
</blockquote>
<pre wrap=""><!---->
Hehe, but the "relational" means that the data is stored in relations,
not that the data has to be related to other data.
</pre>
</blockquote>
Fair call<br>
<blockquote cite="mid200312082138.54363.peter_e@gmx.net" type="cite">
<pre wrap=""></pre>
<blockquote type="cite">
<pre wrap="">If someone could give me an idea of how far away this fix is, I'd be
grateful.
</pre>
</blockquote>
<pre wrap=""><!---->
No one has really expressed intentions to fix this, so I wouldn't hold
my breath.
</pre>
</blockquote>
So it's a case of "if you really want it fixed, fix it yourself"? If
only I knew anything about writing DBMS internals, I would have a shot
at it.<br>
<br>
/me proceeds to not hold breath<br>
<br>
BJ<br>
</body>
</html>
Stephan Szabo wrote:
On Tue, 9 Dec 2003, Brendan Jurd wrote:
Hi all,
I read on the manual page for Inheritance that:
"A limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children. Thus, in the above example,
specifying that another table's column REFERENCES cities(name) would
allow the other table to contain city names but not capital names. This
deficiency will probably be fixed in some future release."I have a few projects that could benefit from inherited table structure,
and it's a very cool idea, but this inability of indexes to include
derived tables is a real functionality-killer. It's not "Object
Relational" if the objects can't be related to anything!If someone could give me an idea of how far away this fix is, I'd be
grateful.I'd say at least 1, probably more versions out. Unique constraints across
an inheritance tree are pretty much a requirement for inherited foreign
keys and I'm not planning to even start thinking about the foreign key
side until after unique's done.As a note, there are workarounds for foreign keys that have been mentioned
in the past. I'm not really sure anyone's post a real good workaround for
unique though.
Wouldn't a central table just holding the unique key values and
maintained by rules/triggers from all the tables in the inheritance tree
do the trick?
That central table would be the referenced one too then, because it
contains the union of all keys.
It would not inherit automagically, but if set up manually that way it
would act correct.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Stephan Szabo wrote:
On Tue, 9 Dec 2003, Jan Wieck wrote:
Stephan Szabo wrote:
On Tue, 9 Dec 2003, Brendan Jurd wrote:
As a note, there are workarounds for foreign keys that have been mentioned
in the past. I'm not really sure anyone's post a real good workaround for
unique though.Wouldn't a central table just holding the unique key values and
maintained by rules/triggers from all the tables in the inheritance tree
do the trick?That central table would be the referenced one too then, because it
contains the union of all keys.That's the general thing for the fks and I guess the insert into the
central table should have the correct behavior in concurrent calls as
well.
I'm not 100% sure though. Is there any possibility that an entry in a
table hides an entry in an inherited one?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Import Notes
Reply to msg id not found: 20031209081755.X56823@megazone.bigpanda.com
On Tue, 9 Dec 2003, Jan Wieck wrote:
Stephan Szabo wrote:
On Tue, 9 Dec 2003, Brendan Jurd wrote:
As a note, there are workarounds for foreign keys that have been mentioned
in the past. I'm not really sure anyone's post a real good workaround for
unique though.Wouldn't a central table just holding the unique key values and
maintained by rules/triggers from all the tables in the inheritance tree
do the trick?That central table would be the referenced one too then, because it
contains the union of all keys.
That's the general thing for the fks and I guess the insert into the
central table should have the correct behavior in concurrent calls as
well.