Re: Enforce primary key on every table during dev?

(Melvin's mail doesn't quote properly; I've tried to fix it but may have broken something)

> On Mar 1, 2018, at 8:50 AM, Melvin Davidson <melvin6925(at)gmail(dot)com> wrote:
>
>
> On Thu, Mar 1, 2018 at 11:43 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>
>
>> On 1 March 2018 at 17:22, Steven Lembark <lembark(at)wrkhors(dot)com> wrote:
>>> If you have a design with un-identified data it means that you havn't
>>> normalized it properly: something is missing from the table with
>>> un-identifiable rows.
>>
>> While that holds true for a relational model, in reporting for
>> example, it is common practice to denormalize data without a
>> requirement to be able to identify a single record. The use case for
>> such tables is providing quick aggregates on the data. Often this
>> deals with derived data. It's not that uncommon to not have a primary
>> or even a uniquely identifiable key on such tables.
>>
>> I do not disagree that having a primary key on a table is a bad thing,
>> but I do disagree that a primary key is a requirement for all tables.
>>
>> More generally: For every rule there are exceptions. Even for this one.
>
> You may perceive that to be "common practice", but in reality it is not, and in fact a bad one. As was previously stated, PosgreSQL is a _relational_ database,
> and breaking that premise will eventually land you in very big trouble. There is no solid reason not to a primary key for every table.

Sure there is. It's an additional index and significant additional insert / update overhead.
If you're never going to retrieve single rows, nor join in such a way that uniqueness
on this side is required there's no need for a unique identifier.

It's a rare case that you won't want a primary key, and I'll often add
a surrogate one for convenience even when it's not actually needed,
but there are cases where it's appropriate not to have one, even in
OLTP work. Log tables, for example.

"Every table should have a primary key, whether natural or surrogate"
is a great guideline, and everyone should follow it until they understand
when they shouldn't.

More generally: For every rule there are exceptions. Even for this one.

Cheers,
Steve