X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 99B559DC9F7 for ; Tue, 27 Dec 2005 13:59:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61414-08 for ; Tue, 27 Dec 2005 13:59:03 -0400 (AST) X-Greylist: delayed 00:06:40.095926 by SQLgrey- Received: from 10.hostcorporate.com (unknown [69.41.231.186]) by postgresql.org (Postfix) with SMTP id 1FE979DC879 for ; Tue, 27 Dec 2005 13:59:01 -0400 (AST) Received: (qmail 3881 invoked from network); 27 Dec 2005 17:30:10 -0000 Received: from localhost (HELO localhost.localdomain) (127.0.0.1) by 10.hostcorporate.com with SMTP; 27 Dec 2005 17:30:10 -0000 Message-ID: <43B17F4B.4010007@zara.6.isreserved.com> Date: Wed, 28 Dec 2005 00:52:11 +0700 From: David Garamond User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: order and order line item Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[UPPERCASE_25_50=0] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/207 X-Sequence-Number: 23749 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