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