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?
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column.
There are five types of constraints in SQL Server: Primary Key Constraint, Foreign Key Constraint, Unique Constraint, Check Constraint and Default Constraint .
Which SQL constraint do we use to set some value to a field whose value has not been added explicitly? Explanation: The DEFAULT constraint is used to set a default value for a column which comes into use when a value for a field in that column is not set.
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.
A trigger, a constraint, and an additional column.
Starting from the end:
The additional column stores the value that is to be 'fixed':
ALTER TABLE ADD SavedGrade int
The constraint restricts the change of the Grade
column:
ALTER TABLE Students
ADD CONSTRAINT CK_Grade CHECK (Finalised = 'false' OR Grade = SavedGrade)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With