Thread: CHECK constraint removing brackets

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

On Mon, Jan 11, 2010 at 12:49 PM, Andy Shellam
<andy-lists@networkmail.eu> wrote:
> 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)
 

Nothing can = null.  and invoice_id IS NULL is the proper
nomenclature.  Also, something <> NULL makes no sense, because we
don't know what NULL is, so that becomes something IS NOT NULL

Also != is not proper SQL, although many dbs understand it, <> is the
proper way to write NOT EQUAL TO.

> 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_stateAND invoice_id <> NULL::integer
 

ANDs have priority of ORs so the removal of the parenthesis makes no
great change here.  also, SQL standard is <> not !=.

I'm guessing the real problems here are your NULL handling.  See if
changing it to IS NULL / IS NOT NULL gets you what you want.

Andy Shellam <andy-lists@networkmail.eu> writes:
> 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_stateAND invoice_id <> NULL::integer
 

There is no "conversion" there, that means exactly the same thing.
(AND binds tighter than OR.)

I think your real problem is that you're trying to use "= NULL" and
"!= NULL" where you should say IS NULL or IS NOT NULL.
        regards, tom lane

Hi Tom and Scott,

>
> I think your real problem is that you're trying to use "= NULL" and
> "!= NULL" where you should say IS NULL or IS NOT NULL.

Argh such a school-boy error!  This is the first bit of database programming I've done for about 2 months, and I hadn't
switchedmy C++ brain off. 

I know about the <> and !=, for some reason != has always made better sense to me to read, so I tend to write it that
way.

Cheers,
Andy

Andy Shellam <andy-lists@networkmail.eu> writes:
> I know about the <> and !=, for some reason != has always made better sense to me to read, so I tend to write it that
way.

Yeah, a lot of people prefer != ... that's why we provide it as an
alias for <>.  There's no functional difference.
        regards, tom lane