Обсуждение: 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. Thisis different from having a UNIQUE index on each individual column. In the former case, all of the columns together mustbe unique; in the latter case, as you mention, each index is evaluated separately. > 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! >> >> 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