On Mon, May 11, 2020 at 9:56 AM Peter Devoy <peter@3xe.co.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.
