db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre Tjeldvoll <Dyre.Tjeldv...@oracle.com>
Subject Re: Unique constraints and nulls
Date Thu, 15 May 2014 07:46:35 GMT
On 05/14/2014 04:35 PM, John English wrote:
> On 14/05/2014 16:52, John English wrote:
>> Is there an easy way to constrain the values of A to be unique even
>> when B is
>> null? (I could try to change things so that empty strings are used
>> instead of
>> nulls, but that would involve changing existing code and it will take
>> quite a
>> bit of work to ensure that there aren't any unexpected knock-on
>> effects, so I
>> prefer to stick with nulls if I can.)
>
> Further investigation reveals that B is nullable for a reason: it's an
> optional value which is a foreign key if present. What I'm now trying to
> do is delete before inserting, using a prepared statement inside a
> transaction:
>
>    delete from Foo where A=? and B=?
>
> However, when the value for B is null, nothing gets deleted (presumably
> due to the query containing B=NULL rather than B IS NULL).
>
> Can anyone suggest a way to fix this?

Could you not do

DELETE FROM FOO WHERE A = ? AND ( B = ? OR B IS NULL )

?


-- 
Regards,

Dyre

Mime
View raw message