473,289 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
6 2751
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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2
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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #3
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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #4
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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #5
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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #6
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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed...
1
by: Vinodh Kumar P | last post by:
I understand the number of foreign keys allowed is restricted by the DBMS I use. In a general relational schema design perspective how many foreign keys a table shall have? If I have large number...
0
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A...
4
by: Bruno Baguette | last post by:
Hello, I've a strange problem with inheritance on PostgreSQL 7.4.1. It seems like tables don't inherits the primary key of the 'mother' table. Here's a little sample to show my problem : ...
5
by: jao | last post by:
My company's product uses Postgres 7.4.3. Postgres is working well for us, and we've worked through many performance issues by tweaking the schema, indexes, and posgresql.conf settings. ...
2
by: Ian Davies | last post by:
I have created a database with about 17 tables. I have been creating foreign keys some of which have worked but when creating others I get the message below ************************* 1005...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
1
by: rbarber | last post by:
I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables...
1
by: apax999 | last post by:
Kinda new to SQL, using SQL Server 2005. I have some foreign keys in a couple of tables. I need to drop these tables, but can't since I'll get the error: Msg 3726, Level 16, State 1, Line...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.