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@gmail.com 
> <mailto:finzelj@gmail.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.klaver@aklaver.com