是否可以使用SQL约束来防止在条件成立时更改特定值？ [英] Can an SQL constraint be used to prevent a particular value being changed when a condition holds?

问题描述

我知道SQL约束可以强制数据满足有效性标准。然而，如学生的成绩只能在完成标志为假时更新的标准怎么样？这种更新标准必须由应用程序处理？

解决方案

简单的答案：不，SQL约束本身不能阻止更改 几种SQL约束：CHECK，DEFAULT，NOT NULL，UNIQUE，Primary Key和Foreign Key。

每个都可以单独或组合地限制或影响列的值，但不能阻止UPDATE到允许的值。特别地，这些约束没有一个可以防止基于先前的成绩和完成值的UPDATE到成绩和/或完成。

UPDATE触发器可以这样做：比较新的和旧的成绩值，如果这些不同，并且Finalized ='true'，回滚UPDATE与解释性的错误消息。

然而，应用程序可以，一个业务规则更优雅。规则本身可以使用一点澄清关于何时可以改变Finalized值。例如，是否允许同时更改成绩并设置Finalized ='false'？触发器逻辑可以处理这样的细节，并且作为故障保护来安装它是合理的，同时使规则在应用程序（前端/中间件/后端）中的某处明确。

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?

解决方案

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.

这篇关于是否可以使用SQL约束来防止在条件成立时更改特定值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！