Re: Enforce primary key on every table during dev?

On 02/28/2018 05:52 AM, John McKown wrote:
> On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel <finzelj(at)gmail(dot)com
> <mailto:finzelj(at)gmail(dot)com>>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

Then you would get this:

test=# CREATE TABLE foo (filler text primary key default null, realcol1
int, realcol2 text);
CREATE TABLE
test=# insert into foo (realcol1, realcol2) values (1, 'test');
ERROR: null value in column "filler" violates not-null constraint
DETAIL: Failing row contains (null, 1, test).

> 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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com