order and order line item

From: David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: order and order line item
Date: 2005-12-27 17:52:11
Message-ID: 43B17F4B.4010007@zara.6.isreserved.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Suppose we have the usual order & order line item entities:

CREATE TABLE "order" (
id INT PRIMARY KEY,
date DATE NOT NULL
);

CREATE TABLE orderlineitem (
id INT PRIMARY KEY,
orderid INT REFERENCES "order"(id),
seq INT NOT NULL,
CONSTRAINT con1 UNIQUE (orderid, seq),
productid INT NOT NULL,
quantity INT NOT NULL,
price NUMERIC(18,4) NOT NULL
);

how do we enforce things like, for example: a) each order must contain
at least 1 line item; or b) each order must have a non-zero total value;
without making INSERT painful? I'm new to triggers, so I'm thinking of
something like a "before COMMIT" trigger but there's no such thing
apparently.

Regards,
dave

Browse pgsql-sql by date

  From Date Subject
Next Message David Garamond 2005-12-27 17:52:18 constraint and ordered value
Previous Message George Pavlov 2005-12-27 17:11:20 Re: The Information Schema vs the PG Catalog