触发器与检查约束 [英] Trigger vs. check constraint
问题描述
我想在表上添加字段级验证。有一个名为account_number的字段，此字段应始终通过luhn检查。我发现一个名为luhn_verify的函数似乎正常工作（如果您有兴趣，则为Google）。它返回一个布尔值。我的问题是：
I want to add a field-level validation on a table. There is a field named "account_number" and this field should always pass a "luhn" check. I've found a function called "luhn_verify" that seems to work properly (google for it if you are interested). It returns a boolean. My question is:
PostgreSQL在使用触发器进行此验证与检查约束有任何主要的性能优势。
Are there any major performance advantages in PostgreSQL for using a trigger for this validation vs. a check constraint.
其他信息：
- PostgreSQL 9.1
- 表当前没有插入触发，但确实有更新。
免责声明：
我觉得像这样可能已经被回答了，但我似乎找不到明确的答案。如果是，请标记为重复并引用原始问题/答案。
I feel like this has probably already been answered, but I can't seem to find a distinct answer. If so, please mark as duplicate and reference original question/answer.
可能是dba板的更好的问题。
Might be a better questions for the dba board.
推荐答案
经验法则是在诉诸触发条件之前使用 CHECK 约束。
The rule of thumb is to use CHECK constraint before you resort to triggers.
A CHECK 约束更快，更简单，更便于携带，需要更少的代码，更容易出错。触发器可以很容易被其他触发器所规避。
A CHECK constraint is faster, simpler, more portable, needs less code and is less error prone. Triggers can easily be circumvented by other triggers, for instance.
如果 CHECK 约束对您的情况太限制或导致重新加载转储的麻烦，您可以使用 NOT VALID 修饰符作为中间（pg 9.2+）。详细信息：
If a CHECK constraint is too restrictive for your case or causes trouble reloading a dump, you could use the NOT VALID modifier as middle ground (pg 9.2+). Details:
- Disable all constraints and table checks while restoring a dump
这篇关于触发器与检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
