数据库完整性：触发器与键/约束
我和我的朋友就数据库的设计争论不休。
他认为，为了确保复杂数据库的完整性，最好使用触发器。
我相信为此目的最好使用键（主键、唯一键）和约束。
我认为使用触发器是危险的，因为它们在“幕后”工作，并且不容易说出命令执行后会发生什么。此外，如果触发器有错误，它可能会破坏数据库的完整性。
你对此有何看法？
I and my friend argued with each other about the design of a database.
He argues that to ensure the integrity of a complex database is better to use triggers.
I believe that for this purpose is better to use keys (primary, unique), and constraints.
I think that the use of triggers is dangerous, because they work "behind the scene" and it's not easy to say what will happen after the execution of a command. Moreover, if trigger has a bug it can break DB's integrity.
What do you think about it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
“这是关于该主题的 AskTom 讨论。在这个问题上没有硬性规定（否则就不会有辩论！）...”
是的。声明式总是比程序性实现更好。声明式不太容易出错。声明式更容易维护。声明性比程序性实施更具自我记录性。声明式为 DBMS 提供了优化的最佳时机，并且大多数时候 DBMS 是比程序员更好的优化器。
程序实现的唯一优点是，如果有真正的声明性约束可用，它意味着那些没有约束的人会找到工作，而不仅仅是我们从 SQL 中得到的可怜的 PK+FK。
"Here is an AskTom discussion on the topic. There is no hard and fast rule on the issue (else there would be no debate!)..."
Yes there is. Declarative is always better than procedurally implemented. Declarative is less prone to making errors. Declarative is easier to maintain. Declarative is more self-documenting than procedurally implemented. Declarative offers the best occasion for the DBMS to optimize, and the DBMS IS a better optimizer than the programmer, most of the time.
The only advantage in procedurally implemented is that it means jobs for those who would be without one if true declarative constraints were available, not just the poor PK+FK that we get from SQL.
实际上，您并没有说出为什么您的朋友会这么想，但是，在任何情况下，约束/键都是确保数据完整性的标准、定义和正确方法，原因有两个：
每个人都知道它们，您会避免使用它们违反了最小意外原则。
它们已经实施、测试并运行。
滚动您自己的数据完整性代码并没有实际的好处。触发器适用于其他用例，例如（例如）保留所有插入的日志。
You actually don't say why your friend thinks what he thinks, but, in any case constraints/keys are the standard, defined and proper way to ensure data integrity, for two reasons:
Everybody knows them and you'll avoid violating the principle of least surprise by using them.
They are already implemented, tested and working.
There is no actual benefit of rolling your own data integrity code. Triggers are intended for other use cases, such as (for example) keeping a log of all insertions.
您没有指定什么数据库，但我会假设 ANSI 标准的关系 DBMS，例如 Oracle 或 SQL Server。
我想这取决于你所说的诚信是什么意思。如果您只是想将子记录和父记录保留在一起并防止出现孤记录，那么使用主键和外键约束的内置 RI 是可行的方法。
如果您的 RI 比较复杂，例如父记录中的字段 1 > > 100 那么子记录中的字段 2 必须 < 200. 必须使用触发器。
我不会使用触发器来执行简单的 RI，那个轮子已经被发明了。
You didn't specify what database but I'll assume an ANSI standard, relational DBMS such as Oracle or SQL Server.
I guess it depends on what you mean by integrity. If you are only trying to keep child records and parent records all together and prevent orphans then built-in RI using primary and foreign key constraints is the way to go.
If your RI is more complicated, for example if field 1 in parent record is > 100 then field 2 in child record must be < 200. Triggers must be used.
I would not use triggers to enforce simple RI, that wheel has already been invented.
我不认为这是明确的一种方式或另一种方式，但是fwiw，我倾向于对可以在 DRI 约束中完成的任何事情使用 DRI 约束，并为那些无法在 DRI 中完成的事情保存触发器约束（例如防止日期范围重叠）
I don't think it's clear cut one way or the other, but fwiw, I tend to use DRI constraints for anything that can be done in a DRI constraint, and save the triggers for those things that can't be done in a DRI Constraint (like preventing overlapping dateranges)