On Wed, Feb 28, 2018 at 4:07 PM, Tim Cross <theophilusx@xxxxxxxxx> wrote:
Jeremy Finzel <finzelj@xxxxxxxxx> writes:
> 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 .
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
