On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer <hjp-pgsql@xxxxxx> 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.
