Re: Enforce primary key on every table during dev? - Mailing list pgsql-general

From Melvin Davidson
Subject Re: Enforce primary key on every table during dev?
Date
Msg-id CANu8FiwEYzfFu3rVZvxUu1fZg1VuJ=BTckLWW8D4rjEONs-V-w@mail.gmail.com
Whole thread Raw
In response to Re: Enforce primary key on every table during dev?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general


On Wed, Feb 28, 2018 at 8:57 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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


As Adrian pointed out, by definition, PK's create a constraint which are NOT NULLABLE;

Here is the SQL to check for tables with no primary key.

SELECT n.nspname,
               c.relname as table,
               c.reltuples::bigint
   FROM pg_class c
     JOIN pg_namespace n ON (n.oid =c.relnamespace )
 WHERE relkind = 'r' AND
               relhaspkey = FALSE
ORDER BY n.nspname, c.relname;


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Enforce primary key on every table during dev?
Next
From: John McKown
Date:
Subject: Re: Enforce primary key on every table during dev?