Inheritance and referential integritry in 7.0.3 - Mailing list pgsql-general

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)