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
