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 <[EMAIL PROTECTED]> 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
