Re: Enforce primary key on every table during dev?

> On 1 Mar 2018, at 1:47, Melvin Davidson <melvin6925(at)gmail(dot)com> wrote:

> > I think you would be better off having an automated report which alerts
> >you to tables lacking a primary key and deal with that policy through
> >other means.
>
> Perhaps a better solution is to have a meeting with the developers and explain to them
> WHY the policy of enforcing a primary key is important. Also, explain the purpose of
> primary keys and why it is not always suitable to just use an integer or serial as the key,
> but rather why natural unique (even multi column) keys are better. But this begs the question,
> why are "developers" allowed to design database tables? That should be the job of the DBA! At
> the very minimum, the DBA should be reviewing and have the authority to approve of disapprove
> of table/schema designs/changes .

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. It will have lots of foreign key references to other tables, but rows containing the same values are probably not duplicates.
Adding a surrogate key to such a table just adds overhead, although that could be useful in case specific rows need updating or deleting without also modifying the other rows with that same data - normally, only insertions and selections happen on such tables though, and updates or deletes are absolutely forbidden - corrections happen by inserting rows with an opposite transaction.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.