INSERT UNIQUE row?
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?
Thanks,
Pierre
A UNIQUE index can have any number of columns, so you can create an index with all of the appropriate columns listed. This is different from having a UNIQUE index on each individual column. In the former case, all of the columns together must be unique; in the latter case, as you mention, each index is evaluated separately.
Show quoted text
On Jul 9, 2023, at 15:58, pf@pfortin.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?
Thanks,
Pierre
On Sun, Jul 9, 2023 at 3:58 PM <pf@pfortin.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.
On 7/9/23 15:58, pf@pfortin.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...
Does the locality you are in have something like the Property ID# and/or
Parcel # / Geo ID shown here:
https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
Each row is only unique as an entity; but the columns are not...
Is this covered in the docs?
Thanks,
Pierre
--
Adrian Klaver
adrian.klaver@aklaver.com
On 7/9/23 17:04, Adrian Klaver wrote:
On 7/9/23 15:58, pf@pfortin.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...Does the locality you are in have something like the Property ID# and/or
Parcel # / Geo ID shown here:https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
Should have added, can you create your own surrogate PK to fill the same
role?
Each row is only unique as an entity; but the columns are not...
Is this covered in the docs?
Thanks,
Pierre
--
Adrian Klaver
adrian.klaver@aklaver.com
On Sun, 9 Jul 2023 17:04:03 -0700 Adrian Klaver wrote:
On 7/9/23 15:58, pf@pfortin.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...Does the locality you are in have something like the Property ID# and/or
Parcel # / Geo ID shown here:https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
Thanks! Just getting started on this issue and this made me realize my
current data source may not be the best... Much appreciated!
Show quoted text
Each row is only unique as an entity; but the columns are not...
Is this covered in the docs?
Thanks,
Pierre
On Sun, 9 Jul 2023 16:42:15 -0700 David G. Johnston wrote:
Yes, the mechanics of defining multi-column unique constraints on tables is
covered in the docs.
Good to know I'm not searching in vain...
On 10/07/2023 04:25 CEST pf@pfortin.com wrote:
On Sun, 9 Jul 2023 17:04:03 -0700 Adrian Klaver wrote:
On 7/9/23 15:58, pf@pfortin.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...Does the locality you are in have something like the Property ID# and/or
Parcel # / Geo ID shown here:https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0
Thanks! Just getting started on this issue and this made me realize my
current data source may not be the best... Much appreciated!
Also keep in mind that it's not trivial to model addresses, even in a single
country. Some database constraints may become a footgun.
https://www.mjt.me.uk/posts/falsehoods-programmers-believe-about-addresses/
--
Erik