> On May 11, 2020, at 12:55 PM, Peter Devoy <peter@xxxxxxxxx> wrote: > > Hi list > > I need to store addresses for properties (as in real estate) so in my > naivety I created a unique constraint like this: > > ALTER TABLE properties > ADD CONSTRAINT is_unique_address > UNIQUE ( > description, --e.g. Land north of Foo Cottage > address_identifier_general, > street, > postcode > ); > > Of course, if any of the fields are NULL (which they often are) I end > up with duplicates. > > One solution may be to add NOT NULL constraints and use empty strings > instead of NULL values but, until asking around today, I thought this was > generally considered bad practice. > > Please can anyone recommend a way of approaching this? Perhaps empty strings > are pragmatic in this situation? Hi Peter, I wouldn’t use empty strings in place of NULL. It’s possible to define a partial unique index that has more or less the same effect as a constraint. Have you looked into them? Cheers Philip
