Re: Enforce primary key on every table during dev? - Mailing list pgsql-general

On 1 March 2018 at 17:22, Steven Lembark <lembark@wrkhors.com> wrote:
>
>> On 03/01/2018 02:20 AM, Alban Hertroys wrote:
>> [snip]
>> > Not to mention that not all types of tables necessarily have
>> > suitable candidates for a primary key. You could add a surrogate
>> > key based on a serial type, but in such cases that may not serve
>> > any purpose other than to have some arbitrary primary key.
>> >
>> > An example of such tables is a monetary transaction table that
>> > contains records for deposits and withdrawals to accounts.

(...)

> Start with Date's notion that a database exists to correclty represent
> data about the real world. Storing un-identified data breaks this
> since we have no idea what the data means or have any good way of
> getting it back out. Net result is that any workable relational
> database will have at least one candidate key for any table in it.

(...)

> 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.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.