Issue Details (XML | Word | Printable)

Key: CORE-1934
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Alexander Tyutik
Votes: 1
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Stationary constraint for columns

Created: 12/Jun/08 07:20 AM   Updated: 20/May/09 06:46 PM
Component/s: Engine
Affects Version/s: 2.5 Initial
Fix Version/s: None


 Description  « Hide
I propose to implement "stationary" constraint (or any other name you like).
This constraint means that value of the field can't be changed with UPDATE command.

Sample:

CREATE TABLE zzz (
   ID INTEGER NOT NULL PRIMARY KEY STATIONARY,
   ...
);

For example i have many tables with field ID (PK), which should not be changed. For this propose i wrote triggers like this:

CREATE TRIGGER rrr FOR zzz ACTIVE BEFORE UPDATE AS
BEGIN
  IF (NEW.ID IS DISTINCT FROM OLD.ID) THEN
    EXCEPTION ERROR 'ID changing is not allowed for table zzz';
END

It will be very conveniently if server will be able to make such checking automatically.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Ain Valtin added a comment - 12/Jun/08 08:55 AM
I have been thinking about similar feature to reduce the amount of this kind of triggers. It would be like calculated field, but vale stored into table, something like

FieldName Type AUTO [constraint name] ON {INSERT | UPDATE} expression

it could be used to generate PKs, timestamps etc:

CREATE TABLE zzz (
   ID INTEGER NOT NULL PRIMARY KEY AUTO ON INSERT GEN_ID(GEN_zzz, 1),
   Changed TIMESTAMP AUTO ON INSERT Current_Timestamp ON UPDATE Current_Timestamp,
  ChangedBy VARCHAR AUTO ON INSERT Current_USER ON UPDATE Current_USER,
   ...
);


Alexander Tyutik added a comment - 12/Jun/08 09:20 AM
2 Ain Valtin

I see few problems in your proposition.

If such field will be readonly, than you will have:
1) Problems with shortened commands like INSERT INTO TABLE VALUES (...)
2) You will need to exclude such fields from INSERT/UPDATE commands

If such field will not readonly, than what will you do if user set value for this field?

Before this post i thought about AUTOINCREMENT clause for linking field with generator, but it's not exactly what i need. Constraint like 'STATIONARY' may be used with any field, not only autoincrements...

Cosmin Apreutesei added a comment - 07/Nov/08 02:51 PM - edited
IMHO Ain's idea is semantically compatible with creating before-insert triggers for doing the same thing. Actually, as I see it it's just syntax sugar for creating those triggers (you can even implement it that way internally). Access to "VALUE" as in CHECK constraint definitions would also be helpful and would solve the case outlined in CORE-1311 (i.e. we would not be using default values anymore).


Ain Valtin added a comment - 19/May/09 04:26 PM
Alexander - I guess the problems you raise apply to ordinary computed fields as well so they aren't really that important (ie new AUTO fields should / could act as old ones in that respect). I don't use computed fields myself so I don't konw how they work of the top of my head, but my first reaction would be that error should be raised if user attepts to assing a value to such a field.

Adriano dos Santos Fernandes added a comment - 19/May/09 06:39 PM
FWIW, I already proposed similar thing in fb-architect but it was rejected AFAIR.

Ain Valtin added a comment - 20/May/09 06:46 PM
Another kind of similar request: http://tracker.firebirdsql.org/browse/CORE-2463