Re: Enforce primary key on every table during dev?

On Wed, Feb 28, 2018 at 4:07 PM, Tim Cross <theophilusx(at)gmail(dot)com> wrote:

>
> Jeremy Finzel <finzelj(at)gmail(dot)com> writes:
>
> > We want to enforce a policy, partly just to protect those who might
> forget,
> > for every table in a particular schema to have a primary key. This can't
> > be done with event triggers as far as I can see, because it is quite
> > legitimate to do:
> >
> > BEGIN;
> > CREATE TABLE foo (id int);
> > ALTER TABLE foo ADD PRIMARY KEY (id);
> > COMMIT;
> >
> > It would be nice to have some kind of "deferrable event trigger" or some
> > way to enforce that no transaction commits which added a table without a
> > primary key.
> >
>
> 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. Using triggers in this way often leads to unexpected
> behaviour and difficult to identify bugs. The policy is a management
> policy and probably should be dealt with via management channels rather
> than technical ones. Besides, the likely outcome will be your developers
> will just adopt the practice of adding a serial column to every table,
> which in itself doesn't really add any value.
>
> Tim
>
>
> --
> Tim Cross
>
>

*> 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 ofprimary 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! Atthe very minimum, the DBA should be
reviewing and have the authority to approve of disapprove of table/schema
designs/changes .*

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!