Re: A form of inheritance with PostgreSQL

Just a little example of what I've been using on version 8.0.3 with total satisfaction.

CREATE TABLE "public"."tblMovementDetails" (
"ID" INTEGER NOT NULL,
"PlanningDetailID" INTEGER NOT NULL,
"MovementID" INTEGER NOT NULL,
"UserID" VARCHAR(5) NOT NULL,
"Number" INTEGER DEFAULT 0 NOT NULL,
"ChangeDate" DATE NOT NULL,
CONSTRAINT "PK_tblMovementDetails" PRIMARY KEY("ID")
) WITHOUT OIDS;

CREATE RULE "disallow_delete" AS ON DELETE TO "public"."tblMovementDetails"
DO INSTEAD NOTHING;

CREATE RULE "disallow_insert" AS ON INSERT TO "public"."tblMovementDetails"
DO INSTEAD NOTHING;

CREATE RULE "disallow_update" AS ON UPDATE TO "public"."tblMovementDetails"
DO INSTEAD NOTHING;

CREATE TABLE "public"."tblDeliveryDetails" (
"InvoiceAmount" NUMERIC(8,2) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_tblDeliveryDetails" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblDeliveryDetails_InvoiceAmount" CHECK ("InvoiceAmount" >= (0)::numeric),
CONSTRAINT "CH_tblDeliveryDetails_Number" CHECK ("Number" >= 0),
CONSTRAINT "FK_tblDeliveryDetails_tblMovement" FOREIGN KEY ("MovementID")
REFERENCES "public"."tblDelivery"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblDeliveryDetails_tblPlanningDetails" FOREIGN KEY ("PlanningDetailID")
REFERENCES "public"."tblPlanningDetails"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;

CREATE INDEX "IDX_tblDeliveryDetails_PlanningDetailID" ON "public"."tblDeliveryDetails"
USING btree ("PlanningDetailID");

CREATE TRIGGER "TRIG_tblDeliveryDetails_Archive" AFTER INSERT OR UPDATE OR DELETE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_archive_delivery"();

CREATE TRIGGER "TRIG_tblDeliveryDetails_ChangeDate" BEFORE INSERT OR UPDATE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_set_changedate"();

CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblDeliveryDetails"
DO (SELECT currval('"tblDeliveryDetails_ID_seq"'::text) AS "ID", new."PlanningDetailID";);

CREATE TABLE "public"."tblOrderDetails" (
"QuoteID" INTEGER NOT NULL,
CONSTRAINT "PK_tblOrderDetails" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblOrderDetails_Number" CHECK ("Number" >= 0),
CONSTRAINT "FK_tblOrderDetails_tblMovement" FOREIGN KEY ("MovementID")
REFERENCES "public"."tblOrder"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblOrderDetails_tblPlanningDetails" FOREIGN KEY ("PlanningDetailID")
REFERENCES "public"."tblPlanningDetails"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblOrderDetails_tblQuote" FOREIGN KEY ("QuoteID")
REFERENCES "public"."tblQuote"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;

CREATE TRIGGER "TRIG_tblOrderDetails_ChangeDate" BEFORE INSERT OR UPDATE
ON "public"."tblOrderDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_set_changedate"();

CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblOrderDetails"
DO (SELECT currval('"tblOrderDetails_ID_seq"'::text) AS "ID", new."PlanningDetailID";);

CREATE TABLE "public"."tblSendingDetails" (
"HandlingCode" VARCHAR(10) DEFAULT ''::character varying,
CONSTRAINT "PK_tblSendingDetails" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblSendingDetails_Number" CHECK ("Number" >= 0),
CONSTRAINT "FK_tblSendingDetails_tblMovement" FOREIGN KEY ("MovementID")
REFERENCES "public"."tblSending"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblSendingDetails_tblPlanningDetails" FOREIGN KEY ("PlanningDetailID")
REFERENCES "public"."tblPlanningDetails"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;

CREATE INDEX "IDX_tblSendingDetails_PlanningDetailID" ON "public"."tblSendingDetails"
USING btree ("PlanningDetailID");

CREATE TRIGGER "TRIG_tblSendingDetails_Archive" AFTER INSERT OR UPDATE OR DELETE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_archive_sending"();

CREATE TRIGGER "TRIG_tblSendingDetails_ChangeDate" BEFORE INSERT OR UPDATE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_set_changedate"();

CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblSendingDetails"
DO (SELECT currval('"tblSendingDetails_ID_seq"'::text) AS "ID", new."PlanningDetailID";);

>>> Greg Toombs <greg(dot)toombs(at)bluebottle(dot)com> 2007-03-08 19:01 >>>
Hello.

I'm trying to figure out how to nicely implement a C++ class-like system with PostgreSQL. Consider the following:

Tables Fruit, Apple, Orange

I want to design the foreign key scheme such that there are relations between fruit and apple, and fruit and orange, that imply that apple is a fruit, and orange is a fruit.

I don't want to eliminate the existence of Apple and Orange tables, because there will be columns specific to both Apple and Orange; if I include these columns in Fruit, then if Fruit is an Orange, the Apple columns will be needlessly present in Apple rows.

The different ways of implementing this scheme that I've thought of (some uglier than others):

- Have Fruit contain foreign keys to both Apple and Orange, and write a check constraint in Fruit specifying that exactly one of (Apple FK, Orange FK) needs to be non-null. The disadvantage of this method is that it isn't exactly loosely coupled. For every other fruit type table I implemented I'd have to go back and add a foreign key in Fruit.

- Have a foreign key in Apple to Fruit, and in Orange to Fruit; then somehow create a constraint that imposes uniqueness on the union of foreign keys in both Apple and Orange. To figure out what type of fruit a Fruit row is, run a query for foreign keys in Orange and Apple matching the primary key of Fruit. You'd also want to somehow create a constraint that the result of this query should always return exactly one row (perhaps with a trigger?)

Any advice will be appreciated! As I'm relatively new to Postgre, I might need some help with the actual implementation as well.

Thank you.

- Greg