Re: index unique
| От | David G. Johnston |
|---|---|
| Тема | Re: index unique |
| Дата | |
| Msg-id | CAKFQuwbBZQTLtYRxaJUfGFGj60vmcEYCsxiw3tAj1U12kR3TiA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: index unique ("Peter J. Holzer" <hjp-pgsql@hjp.at>) |
| Ответы |
Re: index unique
|
| Список | pgsql-general |
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.
David J.
В списке pgsql-general по дате отправления: