> 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. >
I think you would be better off having an automated report which alerts you to tables lacking a primary key and deal with that policy through other means. Using triggers in this way often leads to unexpected behaviour and difficult to identify bugs. The policy is a management policy and probably should be dealt with via management channels rather than technical ones. Besides, the likely outcome will be your developers will just adopt the practice of adding a serial column to every table, which in itself doesn't really add any value. Tim
-- Tim Cross
> I think you would be better off having an automated report which alerts >you to tables lacking a primary key and deal with that policy through >other means.
Perhaps a better solution is to have a meeting with the developers and explain to them WHY the policy of enforcing a primary key is important. Also, explain the purpose of primary keys and why it is not always suitable to just use an integer or serial as the key, but rather why natural unique (even multi column) keys are better. But this begs the question, why are "developers" allowed to design database tables? That should be the job of the DBA! At the very minimum, the DBA should be reviewing and have the authority to approve of disapprove of table/schema designs/changes .