db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Unique constraints and nulls
Date Wed, 14 May 2014 14:35:07 GMT
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?
John English

View raw message