Gentlemen,

I'd like to try to recap this discussion. Adriano proposed adding an
attribute to a column definition that would prevent the value in that
column from being changed after it is originally stored. Call it the
WRITE ONCE constraint.

Others have raised the objection that two existing mechanisms can
enforce that restriction - grants and triggers.

Grants restrict update access to that field. Various users, roles,
procedures, and triggers can be granted update access to other fields in
the table, but no one gets update access to the non-update able field,
except whoever it is who needs to go in and fix up the table after some
disastrous data entry errors.

A trigger is the normal way prevent changes - after update position 0 -
abort if old.field <> new.field. Then there's one trigger to deactivate
before the aforementioned data entry error can be fixed.

Jim argues that a constraint that is switched on and off will chew up
record versions. But Firebird now increments the record version number
only when the on disk record format changes - changing a constraint
doesn't do that.

So, the question, as I see it, is do we need a third mechanism? I
suppose one could argue that triggers are procedural and constraints are
declarative, so constraints are a clearer way of expression the
intention of the designer. And the constraint could be disabled to fix
data entry errors.

Is this restriction common enough to be worth specific syntax?

Regards,


Ann