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@jps.net>