From: Andy Shellam <andy-lists@networkmail.eu>
To: pgsql-sql@postgresql.org
Subject: CHECK constraint removing brackets
Date: Mon, 11 Jan 2010 19:49:50 +0000
Message-ID: <D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu> (raw)
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 = '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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andy-lists@networkmail.eu
Subject: Re: CHECK constraint removing brackets
In-Reply-To: <D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox