Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Stationary constraint for columns [CORE1934] #2371

Open
firebird-automations opened this issue Jun 12, 2008 · 7 comments
Open

Stationary constraint for columns [CORE1934] #2371

firebird-automations opened this issue Jun 12, 2008 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alexander Tyutik (tut)

Votes: 1

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 (http://NEW.ID IS DISTINCT FROM http://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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Alexander Tyutik (tut)

description: 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.

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 (http://NEW.ID IS DISTINCT FROM http://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.

=>

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 (http://NEW.ID IS DISTINCT FROM http://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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ain Valtin (ain)

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,
...
);

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

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...

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

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 CORE1311 (i.e. we would not be using default values anymore).

@firebird-automations
Copy link
Collaborator Author

Commented by: Ain Valtin (ain)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

FWIW, I already proposed similar thing in fb-architect but it was rejected AFAIR.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ain Valtin (ain)

Another kind of similar request: CORE2463

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant