Thread: foreign key constraints and inheritence

I have this structure:

CREATE TABLE invoicelineitemtypes (
     invoicelineitemtypeid varchar(36) PRIMARY KEY,
     otherstuff varchar(36)
);

CREATE TABLE invoicelineiteminventorytypes (
     moresetuff varchar(36)
)
INHERITS (invoicelineitemtypes);

CREATE TABLE invoicelines (
     invoicelineitemtypeid varchar(36) REFERENCES
invoicelineitemtypes ON DELETE CASCADE,
     otherstuff varchar(36)
);

INSERT INTO invoicelineiteminventorytypes (invoicelineitemtypeid)
VALUES ('1234');

The following insert:

INSERT INTO invoicelines (invoicelineitemtypeid) VALUES ('1234');

invokes

"insert or update on table "invoicelines" violates foreign key
constraint "invoicelines_invoicelineitemtypeid_fkey"

Now, the rec with id '1234' is in the table
invoicelineiteminventorytypes, but I can't wrap my head around the
fact that

SELECT invoicelineitemtypeid FROM invoicelineitemtypes

yields the rec with invoicelineitemtypeid '1234', yet I can't
reference it in the invoicelines INSERT statement.

Am I missing something? Is there a workaround for this?

PostgreSQL 8.0.1 running on OS X 10.4.2.



Garrett

On Sat, Jul 16, 2005 at 05:39:59PM -0400, Garrett Kalleberg wrote:
>
> Now, the rec with id '1234' is in the table
> invoicelineiteminventorytypes, but I can't wrap my head around the
> fact that
>
> SELECT invoicelineitemtypeid FROM invoicelineitemtypes
>
> yields the rec with invoicelineitemtypeid '1234', yet I can't
> reference it in the invoicelines INSERT statement.

See the inheritance documentation:

http://www.postgresql.org/docs/8.0/static/ddl-inherit.html

"A serious 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.  This
is true on both the referencing and referenced sides of a foreign
key constraint."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi,

> http://www.postgresql.org/docs/8.0/static/ddl-inherit.html
>
> "A serious 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.  This
> is true on both the referencing and referenced sides of a foreign
> key constraint."

I would realy like to see this fixed. But I guess if it was easy then
someone would already have fixed it...

- Sander

On Jul 17, 2005, at 5:35 AM, Michael Fuhr wrote:

> See the inheritance documentation:
>
> http://www.postgresql.org/docs/8.0/static/ddl-inherit.html
>
> "A serious 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.  This
> is true on both the referencing and referenced sides of a foreign
> key constraint."

I think I was just in denial. I'd read it some time ago, but
yesterday I just didn't want to believe it.

What I'd like to believe: that all behaviors (i.e. constraints) are
inherited down the table hierarchy and may be overridden by
identically named behaviors lower in the hierarchy.


Garrett

On Sun, Jul 17, 2005 at 12:38:48PM +0200, Sander Steffann wrote:
> >
> >"A serious 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.  This
> >is true on both the referencing and referenced sides of a foreign
> >key constraint."
>
> I would realy like to see this fixed. But I guess if it was easy then
> someone would already have fixed it...

It's on the developers' TODO list.  I'm sure a patch would be welcome :-)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi,

>> >"A serious 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.  This
>> >is true on both the referencing and referenced sides of a foreign
>> >key constraint."
>>
>> I would realy like to see this fixed. But I guess if it was easy then
>> someone would already have fixed it...
>
> It's on the developers' TODO list.  I'm sure a patch would be welcome :-)

I wish I knew enough about the internals of PostgreSQL to write one :-)
Sander

On Sun, Jul 17, 2005 at 10:33:58PM +0200, Sander Steffann wrote:
> I wish I knew enough about the internals of PostgreSQL to write one :-)
> Sander

Well, there are other TODO items that are much simpler, which would be a
great way to learn more about the internals. :) There's been talk of
creating a list of good TODO items for new coders to tackle, but I don't
think that ever happened.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"