Re: Enforce primary key on every table during dev?

----- Original Message -----
> From: "Tim Cross" <theophilusx@gmail.com>
> Sent: Wednesday, February 28, 2018 4:07:43 PM
> 
> Jeremy Finzel <finzelj@gmail.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
therein 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.
Itincludes a suite of functionality to assess the database schema via automated testing. Part of a rigorous development
environmentmight include using this tool so that any application/database changes be driven by tests, and then your
codereview process would assure that the appropriate tests are added to the pgTAP script to confirm that changes meet a
policystandard 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
toolstogether, so that before a developer is allowed to check in a feature branch, your teams' code review process
maintainsrigorous oversight of modifications.
 

-- B