Hi,
I notice this had been raised as a bug (and subsequently over-ruled) so I'm asking how I can achieve the following
businessrule.
I have an order table which has an invoice_id column that links to an invoice table (an order can only have 1 invoice,
butone 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
stateexcept unconfirmed, the invoice_id must not be null.
With the above in mind, I decided on the following check to enforce this:
(state = 'Unconfirmed'::client.order_state AND invoice_id = NULL) OR (state != 'Unconfirmed'::client.order_state AND
invoice_id!= NULL)
However PostgreSQL (8.4.2) converts this to the following:
state = 'Unconfirmed'::client.order_state AND invoice_id = NULL::integer OR state <> 'Unconfirmed'::client.order_state
ANDinvoice_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
NULLinvoice_id.
How can I achieve the above?
Thanks,
Andy