Re: Inheritance and referential integritry in 7.0.3

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(at)jps(dot)net>