Thread: Prevent inserting document without rows

I have tables for invoice headers and rows:

CREATE TABLE invoiceheader ( id SERIAL PRIMARY KEY, .... );

CREATE TABLE invoicerow ( id SERIAL PRIMARY KEY,
idheader INTEGER NOT NULL REFERENCES invoiceheader ON DELETE CASCADE ON
UPDATE CASCADE,
 ....
 );

Each invoice must have at leat one row.

I want that transaction commit fails if invoice header is inserted without
any row in invoice rows table.

How to prevent inserting invoice headers without rows ?

Andrus.

On Tue, 2005-08-16 at 17:31 +0300, Andrus wrote:
> Each invoice must have at leat one row.
>
> I want that transaction commit fails if invoice header is inserted
> without
> any row in invoice rows table.
>
> How to prevent inserting invoice headers without rows ?

You could have a NOT NULL foreign key in the header table referencing
the detail table.  You then have declarative integrity guaranteeing that
at least one detail entry exists for each header entry.

How your system populates this column is another question.  In this case
maybe some client-side logic would be cleaner than a trigger-based
solution.