From: Andy Shellam <andy-lists@networkmail.eu>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: CHECK constraint removing brackets
Date: Mon, 11 Jan 2010 19:49:50 +0000
Message-Id: <D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu>
To: pgsql-sql@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1077)

Hi,

I notice this had been raised as a bug (and subsequently over-ruled) so =
I'm asking how I can achieve the following business rule.

I have an order table which has an invoice_id column that links to an =
invoice table (an order can only have 1 invoice, but one invoice can =
have multiple orders.)

An order can have either an unconfirmed state, or any other state after =
it's been confirmed.  If an order has the state unconfirmed, the =
invoice_id column must be null, as an invoice won't have been created =
yet.  If an order has any other state except unconfirmed, the invoice_id =
must not be null.

With the above in mind, I decided on the following check to enforce =
this:

(state =3D 'Unconfirmed'::client.order_state AND invoice_id =3D NULL) OR =
(state !=3D 'Unconfirmed'::client.order_state AND invoice_id !=3D NULL)

However PostgreSQL (8.4.2) converts this to the following:

state =3D 'Unconfirmed'::client.order_state AND invoice_id =3D =
NULL::integer OR state <> 'Unconfirmed'::client.order_state AND =
invoice_id <> NULL::integer

This allows both an order state of "unconfirmed" and a non-null =
invoice_id, and an order state of "confirmed" and a NULL invoice_id.

How can I achieve the above?

Thanks,
Andy=