Re: index unique

On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2021-06-03 22:51:55 +0200, Marc Millas wrote:
> > postgres 12 with postgis.
> > on a table we need a primary key and to get a unique combinaison, we
> need 3
> > columns of that table:
> > 1 of type integer,
> > 1 of type text,
> > 1 of type geometry
> >
> > creating the PK constraint doesn work:
>
> I find that if a natural primary key candidate is so complex, it is
> usually better to use a surrogate key.
>

You make this sound like an either-or proposition, but personally it takes
a very exceptional circumstance to forgo defining a unique natural key.
Whether I choose to supplement that with a surrogate key is a different
matter altogether.

In this case identity would seem to make more sense using labels, not
composition. For instance, on a substance table I would have the
identifier for water be "H20", and have the chemical composition of water
be "H20" (not the best example...). In this case ensuring uniqueness of
the formula-to-label dependency would be trivial to implement and I
probably would too - but in the case of geometry I'd just accept that using
an index to do this would not be possible and, if I really needed
reassurance of geometry uniqueness, I would do so in triggers.

David J.