Can an SQL constraint be used to prevent a particular value being changed when a condition holds?

Question

I know that SQL constraints can force data to meet validity criteria. However, what about criteria such as "Student's grade can only be updated when the 'finalised' flag is false"? Do such update criteria have to be handled by the application?

Page

Depending on your RDBMS, you could enforce this with a "before" or "instead of" trigger on the table. – Stokes

Just what Joe said, though check constraints can also be used, depending on database. – Aliped

Answer 1

Short answer: No, SQL constraints cannot in themselves prevent a change to column Grade when Finalized is 'true' (but allow a change otherwise).

There are several kinds of SQL constraints: CHECK, DEFAULT, NOT NULL, UNIQUE, Primary Key, and Foreign Key.

Each of these can limit or affect the values of columns, either singly or in combination, but cannot prevent an UPDATE to values that are allowed. In particular none of these constraints can prevent an UPDATE to Grade and/or Finalized based on the previous values of Grade and Finalized.

An UPDATE trigger can do this: compare the new and old values of Grade, and if these differ and Finalized = 'true', rollback the UPDATE with an explanatory error message.

However the application can and should enforce such a "business rule" more gracefully. The rule itself could use a bit of clarification about when the Finalized value can be changed. E.g., is it allowed to change Grade and set Finalized = 'false' at the same time? The trigger logic can handle such details, and it would be reasonable to install that as a failsafe, while making the rules explicit somewhere in the application (frontend/middleware/backend) as well.

Southwester

Answer 2

A trigger, a constraint, and an additional column.

Starting from the end:

1. The additional column stores the value that is to be 'fixed':

   ALTER TABLE ADD SavedGrade int
   

2. The constraint restricts the change of the Grade column:

   ALTER TABLE Students
   ADD CONSTRAINT CK_Grade CHECK (Finalised = 'false' OR Grade = SavedGrade)
   

3. The trigger updates the additional column when the Grade column gets updated (the following is for SQL Server):

   CREATE TRIGGER StudentsFinaliseGrade
   ON Students AFTER INSERT, UPDATE
   AS
   IF UPDATE(Grade)
     UPDATE Students
     SET SavedGrade = i.Grade
     FROM inserted i
     WHERE i.ID = Students.ID
       AND i.Grade <> i.SavedGrade
   

So, as long as Finalised = 'false', the Grade column may be changed. When it is changed, the value is immediately stored into the SavedGrade column. (We are updating SavedGrade directly, because otherwise the constraint wouldn't allow us to set Finalised to 'true'.) As soon as Finalised is set, you can no longer change the Grade column because of the constraint.

Tablecloth

Answer 3

IMO, I'd say it should be done in either the application or the stored procedure (possibly both), rather than as an actual constraint (among other things, in your specific example, a grade being "finalized" doesn't always mean it's actually final).

However, if I were implementing this as a constraint, I'd use a CHECK constraint (again, using your example)

CONSTRAINT chk_grade CHECK(grade between 0 AND 100 and finalized = 0)

Check the specific syntax on that, but it's where I'd start.

Ellery

How would this prevent updates to grade when the finalized flag is set? Doesn't it prevent the finalized flag from being set? I don't think that's what the OP has in mind. – Southwester

I'd have to double-check the syntax (as I mentioned), but what it /should/ do is verify that finalized = 0 (that is, it's "False") before it allows a range of 0 - 100 to be placed in grade – Ellery

This doesn't work (in SQL Server, anyway) because the check fails when you try to turn 'finalized' to 1. – Chockablock