From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Re: A form of inheritance with PostgreSQL
Date: Fri, 09 Mar 2007 09:28:04 +0100
Message-ID: <45F128A3.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <45F04F8F.8030800@bluebottle.com>
References: <45F04F8F.8030800@bluebottle.com>
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.toombs@bluebottle.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: A form of inheritance with PostgreSQL
In-Reply-To: <45F128A3.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox