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

On Mon, May 11, 2020 at 9:56 AM Peter Devoy <peter(at)3xe(dot)co(dot)uk> wrote:

> 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
> );
>
>

> Please can anyone recommend a way of approaching this? Perhaps empty
> strings
> are pragmatic in this situation?
>

Don't add a unique table constraint (aside from an artificial primary key).

Then, if you can define a problem where you feel having a unique table
constraint over the field is the correct solution - especially given the
fact that you can have missing data in the relevant fields - you should
post the problem and take suggestions on ways to solve it.

David J.