Re: Enforce primary key on every table during dev?

----- Original Message -----
> From: "Tim Cross" <theophilusx(at)gmail(dot)com>
> Sent: Wednesday, February 28, 2018 4:07:43 PM
>
> 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.

I concur with other respondents that suggest this is more of a policy issue. In fact, you yourself identify it right there in the first sentence as a policy issue!

One tool that changed my life (as a PostgreSQL enthusiast) forever is David Wheeler's pgTAP (http://pgtap.org/) tool. It includes a suite of functionality to assess the database schema via automated testing. Part of a rigorous development environment might include using this tool so that any application/database changes be driven by tests, and then your code review process would assure that the appropriate tests are added to the pgTAP script to confirm that changes meet a policy standard such as what you are demanding. I can't imagine doing PostgreSQL development without it now.

Same guy also produced a related tool called Sqitch (http://sqitch.org/) for data base change management. Use these tools together, so that before a developer is allowed to check in a feature branch, your teams' code review process maintains rigorous oversight of modifications.

-- B