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