Re: Enforcing uniqueness on [real estate/postal] addresses

> On May 11, 2020, at 12:55 PM, Peter Devoy <peter(at)3xe(dot)co(dot)uk> 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