Re: INSERT UNIQUE row?

On Sun, Jul 9, 2023 at 3:58 PM <pf(at)pfortin(dot)com> wrote:

> Hi,
>
> Trying to figure out how to insert new property addresses into an
> existing table.
>
> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to
> each column won't work in such a case since there are multiple properties
> * on the same street
> * in the same town
> * with the same number on different streets
> * etc...
>
> Each row is only unique as an entity; but the columns are not...
>
> Is this covered in the docs?
>
>
Yes, the mechanics of defining multi-column unique constraints on tables is
covered in the docs.

This seems like a waste of space though. That is a lot of text data
(though I suppose not as bad so long as you store integer foreign keys for
many of the columns) to put into an index that isn't really even going to
catch typos and other malformed data situations while preventing something
that is basically impossible to encounter in real life. Especially if you
also have separate individual indexes to make searching for specific subset
of the database faster (i.e., everything in a state).

David J.