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

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.. — Mike Christensen, Jan 16 '13 at 20:41
A simple row trigger `ON UPDATE`, checking `NEW` and `OLD` values can do what you want. — Ihor Romanchenko, Jan 16 '13 at 22:09
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. — Andy Lester, Jan 16 '13 at 22:09
@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. — Andy Lester, Jan 16 '13 at 22:11
@AndyLester I edited my title and first sentence to clarify I'm talking about one record with an update. — andyortlieb, Jan 16 '13 at 22:12
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"` — wildplasser, Jan 16 '13 at 22:15
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? — SingleNegationElimination, Jan 17 '13 at 00:39
@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. — andyortlieb, Jan 17 '13 at 16:41

score 8 · Answer 1 · answered Jan 16 '13 at 23:40

8

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.

answered Jan 16 '13 at 23:40

Craig Ringer

307,061
76
688
778

score 3 · Answer 2 · answered Mar 18 '14 at 00:15

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();

score 2 · Accepted Answer · answered Jan 17 '13 at 21:44

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

It's not the answer to my question, but I have to accept it because it's the right answer to a better question. — andyortlieb, Jan 18 '13 at 20:25
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. — frostymarvelous, Dec 12 '17 at 13:46
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`. — Demurgos, Jan 25 '18 at 15:13
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. — Ski, May 09 '18 at 10:49

score 1 · Answer 4 · answered Jan 16 '13 at 22:18

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.

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

4 Answers4

Linked