On 02/28/2018 05:52 AM, John McKown wrote:
On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel <finzelj@xxxxxxxxx <mailto:finzelj@xxxxxxxxx>>wrote: Thanks,
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?
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).
Hum, it's been so long, I totally forgot. Which makes me wonder why the parser doesn't "know" that a default of NULL for a primary key is going to fail anyway and flag it at CREATE time. Oh, well. Thanks.
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@xxxxxxxxxxx
I have a theory that it's impossible to prove anything, but I can't prove it.
Maranatha! <><
John McKown
John McKown
