Re: Enforce primary key on every table during dev?

[John McKown <john.archie.mckown@xxxxxxxxx>]

On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel <finzelj@xxxxxxxxx> wrote:

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.

Any ideas?

Thanks,

Jeremy

​What stops somebody from doing:

CREATE TABLE foo (filler text primary key default null, realcol1 int, realcol2 text);

And then just never bother to ever insert anything into the column FILLER? It fulfills your stated requirement​ of every table having a primary key. Of course, you could amend the policy to say a "non-NULL primary key".

--

I have a theory that it's impossible to prove anything, but I can't prove it.

Maranatha! <><
John McKown