SQL constraint to prevent updating a column based on its prior value

Question

Can a Check Constraint (or some other technique) be used to prevent a value from being set that contradicts its prior value when its record is updated.

One example would be a NULL timestamp indicating something happened, like "file_exported". Once a file has been exported and has a non-NULL value, it should never be set to NULL again.

Another example would be a hit counter, where an integer is only permitted to increase, but can never decrease.

If it helps I'm using postgresql, but I'd like to see solutions that fit any SQL implementation

Corporate

Have you researched anything so far? – Berbera

Might be best off using a trigger for this. Check if the current value is not-null, then throw an error if they try setting it to null. Though if you need a historical change log, you'd have to build that yourself.. – Samos

which DBMS are you using? Oracle? PostgreSQL? – Chimp

A simple row trigger ON UPDATE, checking NEW and OLD values can do what you want. – Tynan

No, you cannot have a constraint that knows anything about anything other than the row itself. You can have constraints based on columns in the row, but not in other tables, and not in the past. – Sidesman

Nothing in my question asks about any other records. – Corporate

@andyortlieb: I know you didn't ask about other records. I just included that tidbit as well because that's also a common question about constraints. – Sidesman

@AndyLester I edited my title and first sentence to clarify I'm talking about one record with an update. – Corporate

For the other updates you'll probably need a lookup table containing the allowed state transitions. The trigger function could simply do a if NOT EXISTS (select oldval,newval from lut where ...) return "rejected" – Farming

What should happen if I query the row to get its current state, delete the row, and then insert another row like the old one but with values I like? – Gabie

@TokenMacGuy This isn't a record that would get deleted. Anybody can break anything if they try--but I just see the database level as a great place to enforce certain rules like these and cause the application to throw an exception--rather than force the application to make an unnecessary query before an update, especially if multiple applications might be accessing this. I think this is a "best effort" technique that would go rather far. So yes, if somebody really wants to break the data--they can. But they don't want to. And if it were a real concern you can revoke the DELETE privilege. – Corporate

Answer 1

One example would be a NULL timestamp indicating something happened, like "file_exported". Once a file has been exported and has a non-NULL value, it should never be set to NULL again.

Another example would be a hit counter, where an integer is only permitted to increase, but can never decrease.

In both of these cases, I simply wouldn't record these changes as attributes on the annotated table; the 'exported' or 'hit count' is a distinct idea, representing related but orthogonal real world notions from the objects they relate to:

So they would simply be different relations. Since We only want "file_exported" to occur once:

CREATE TABLE thing_file_exported(
    thing_id INTEGER PRIMARY KEY REFERENCES(thing.id),
    file_name VARCHAR NOT NULL
)

The hit counter is similarly a different table:

CREATE TABLE thing_hits(
    thing_id INTEGER NOT NULL REFERENCES(thing.id),
    hit_date TIMESTAMP NOT NULL,
    PRIMARY KEY (thing_id, hit_date)
)

And you might query with

SELECT thing.col1, thing.col2, tfe.file_name, count(th.thing_id)
FROM thing 
LEFT OUTER JOIN thing_file_exported tfe
    ON (thing.id = tfe.thing_id)
LEFT OUTER JOIN thing_hits th
    ON (thing.id = th.thing_id)
GROUP BY thing.col1, thing.col2, tfe.file_name

Gabie

It's not the answer to my question, but I have to accept it because it's the right answer to a better question. – Corporate

It's the right answer to the examples, but not to the question. Also, you can still decrement the counter and delete the export relation. This shouldn't be the accepted. – Pyuria

Please note that in the case of preventing the file_exported attribute to be set to null once defined, this answer does not solve the main issue: now you have to prevent the DB from deleting the rows of thing_file_exported. – Hooked

This is a useful suggestion, however it doesn't answer the question and moralize upon which design is better. Without fully knowing the context you can't for sure say that your suggested design is better. Also you can go very far with this idea and treat any kind of update as distinct idea and have append-only/immutable state design. They all got own advantages and disadvantages. – Tonguing

Answer 2

Use a trigger. This is a perfect job for a simple PL/PgSQL ON UPDATE ... FOR EACH ROW trigger, which can see both the NEW and OLD values.

See trigger procedures.

Denizen

Answer 3

lfLoop has the best approach to the question. But to continue Craig Ringer's approach using triggers, here is an example. Essentially, you are setting the value of the column back to the original (old) value before you update.

CREATE OR REPLACE FUNCTION example_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
     new.valuenottochange := old.valuenottochange;
     new.valuenottochange2 := old.valuenottochange2;
     RETURN new;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



DROP TRIGGER IF EXISTS trigger_name ON tablename;
  CREATE TRIGGER trigger_name BEFORE UPDATE ON tablename
    FOR EACH ROW EXECUTE PROCEDURE example_trigger();

Cm

Answer 4

Stored procedures and functions in PostgreSQL have access to both old and new values, and that code can access arbitrary tables and columns. It's not hard to build simple (crude?) finite state machines in stored procedures. You can even build table-driven state machines that way.

Sparling