On Wed, Feb 28, 2018 at 7:57 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 02/28/2018 05:52 AM, John McKown wrote: > >> On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel <finz...@gmail.com >> <mailto:finz...@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). > 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.kla...@aklaver.com > -- I have a theory that it's impossible to prove anything, but I can't prove it. Maranatha! <>< John McKown
