CHECK constraint removing brackets
От | Andy Shellam |
---|---|
Тема | CHECK constraint removing brackets |
Дата | |
Msg-id | D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu обсуждение исходный текст |
Ответы |
Re: CHECK constraint removing brackets
Re: CHECK constraint removing brackets |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: