Thread: Inheritance and referential integritry in 7.0.3

I am using PostgreSQL 7.0.3 and have the following schema:

CREATE TABLE "products" (
        "id" SERIAL NOT NULL PRIMARY KEY,
        "name" text NOT NULL,
        "description" text NOT NULL,
        "brand" text NOT NULL,
        "url" text,
        "weight" float4 NOT NULL,
        "stock" int4 NOT NULL DEFAULT 0,
        "price" decimal(10,2) NOT NULL DEFAULT 0,
        "warranty" int4 NOT NULL
);

There are various other tables inheriting from "products".

CREATE TABLE "products1" INHERITS products (
  "attribute1" text,
  "attribute2" text
);


There is also another table which references products:

CREATE TABLE "properties" (
        "product" int4 NOT NULL REFERENCES products (id) ON DELETE CASCADE,
        "property" text NOT NULL
);


Now, if I insert a row into one of "products" child tables (eg, products1),
then try to reference it in "properties", it does not work as the product id
is only visible if products* is the target table. Changing the REFERENCES
target to product* products a syntax error, and creating a view as the
REFERENCES target is not allowed.

Short of creating another table which stores all product ids, is there a
simple way to make this work?


Cheers,

--
Alastair D'Silva (mob: 0413 485 733)
Networking Consultant
New Millennium Networking (web: http://www.newmillennium.net.au)

"Alastair D'Silva" wrote:
  >I am using PostgreSQL 7.0.3 and have the following schema:
  >
  >CREATE TABLE "products" (
...
  >There are various other tables inheriting from "products".
 ...
  >There is also another table which references products:
...
  >Now, if I insert a row into one of "products" child tables (eg, products1),
  >then try to reference it in "properties", it does not work as the product id
  >is only visible if products* is the target table. Changing the REFERENCES
  >target to product* products a syntax error, and creating a view as the
  >REFERENCES target is not allowed.
  >
  >Short of creating another table which stores all product ids, is there a
  >simple way to make this work?

No.

I have proposed that this should be allowed, but it is not possible
at the moment.

You can refer directly to a descendant table, of course.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Do not be anxious about anything, but in everything,
      by prayer and supplication, with thanksgiving, present
      your requests to God. And the peace of God, which
      transcends all understanding, will guard your hearts
      and your minds in Christ Jesus."   Philippians 4:6,7

On Sun, Apr 08, 2001 at 01:04:16PM +0800, Alastair D'Silva wrote:
> I am using PostgreSQL 7.0.3 and have the following schema:
>
> CREATE TABLE "products" (
>         "id" SERIAL NOT NULL PRIMARY KEY,
>         "name" text NOT NULL,
>         "description" text NOT NULL,
>         "brand" text NOT NULL,
>         "url" text,
>         "weight" float4 NOT NULL,
>         "stock" int4 NOT NULL DEFAULT 0,
>         "price" decimal(10,2) NOT NULL DEFAULT 0,
>         "warranty" int4 NOT NULL
> );
>
> There are various other tables inheriting from "products".
>
> CREATE TABLE "products1" INHERITS products (
>   "attribute1" text,
>   "attribute2" text
> );
>
>
> There is also another table which references products:
>
> CREATE TABLE "properties" (
>         "product" int4 NOT NULL REFERENCES products (id) ON DELETE CASCADE,
>         "property" text NOT NULL
> );
>
>
> Now, if I insert a row into one of "products" child tables (eg, products1),
> then try to reference it in "properties", it does not work as the product id
> is only visible if products* is the target table. Changing the REFERENCES
> target to product* products a syntax error, and creating a view as the
> REFERENCES target is not allowed.
>
> Short of creating another table which stores all product ids, is there a
> simple way to make this work?

Doesn't work with inheritance...

Do instead:

create table "products1" (
   "product_id"  NOT NULL REFERENCES "products" ("id"),
   "attribute1" text,
   "attribute2" text
);

Then:

BEGIN TRANSACTION;
INSERT INTO "products" (...) VALUES (...);
INSERT INTO "products1" ("product_id", "attribute1", "attribute2")
   VALUES (currval('"products_id_seq"'), 'Foo', 'Bar');
COMMIT;

What's with the quotes anyway? Yuck.

--
Eric G. Miller <egm2@jps.net>

> I have proposed that this should be allowed, but it is not possible
> at the moment.

i read your proposal document in the TODO.detail for inheritance. has
your proposal been accepted as an active path for postgres development?
i have a situation that calls for the scenario that Alastair outlined
above. i was about to make exactly the same post when i read his.

this thread is about postgres 7.03, but i haven't seen any evidence that
your proposals have worked their way into 7.1, as i'm using 7.1b4 and am
having exactly the same problem.


> You can refer directly to a descendant table, of course.

for me, this is not a sufficient workaround because i have a superclass
table whose subclasses hold most of the relevant data and it just
strikes me as particularly poor data design to have something like the
following setup.

foo <- sub_foo_left
     <- sub_foo_right

bar <- sub_bar_left
     <- sub_bar_right

where the bar subclasses exist only so they can refer directly to a
descendant table.

yes, this works, but it seems to me that the more useful way is to have
bar be able to stand alone and refer (with proper integrity) to foo
alone rather than needing it's own subclasses in order to refer directly
to the subclasses of foo.

i don't suppose there's any sort of schedule for your inheritance proposals?

-tfo