sql - 如何在sql中强制执行约束？

Question

我有一个关于对我的表强制执行约束的问题。我有一个名为 workson 的表和一个名为 staff 的表，每个员工都有特定的头衔(主管、授权人、经理……)。我需要确保 supervisor 和 authorizer 不能是 workson 表上的同一个员工。他们之间的基数是多对多的。我不知道该怎么做。你能建议我解决这个问题吗？

谢谢

Answer 1

考虑以下查询

SELECT AssignmentNo, StaffNo
  FROM worksOnStaff
 WHERE StaffType = 'supervisor'
INTERSECT 
SELECT AssignmentNo, StaffNo
  FROM worksOnStaff
 WHERE StaffType = 'authorizer'

当表中的数据满足您的约束时，上述查询将为空集，即

CHECK ( NOT EXISTS ( SELECT AssignmentNo, StaffNo
                       FROM worksOnStaff
                      WHERE StaffType = 'supervisor'
                     INTERSECT 
                     SELECT AssignmentNo, StaffNo
                       FROM worksOnStaff
                      WHERE StaffType = 'authorizer' ) );

问题是，很少有 SQL 产品允许在 CHECK 约束中使用子查询。

通常的解决方法是在过程代码中实现相同的逻辑，例如使用触发器或强制用户通过存储过程更新数据(通过删除基表的更新权限)以确保永远不会违反约束。您必须注意正确序列化更新，而不是让整个事情像胶水一样运行。

幸运的是，有一本关于这个主题的好书:

Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars

For implementing constraints that cannot be declared to the DBMS, we prefer to follow the triggered procedural strategy... Like declared constraints, the triggered procedural strategy cannot be subverted; ...is likely to create a more manageable code architecture ...[however] implementing efficient data integrity code for these constraints through triggers is far from being a trivial task... However, you’ll experience that by implementing table constraints regularly and becoming proficient in doing so, implementing table constraints procedurally is in general quite doable...

execution model EM6: On-Transition-Effect-Property Plus Optimized-Query

1. Translate the formal specification into a constraint validation query.
2. Develop code to maintain transition effects.
3. Devise transition effect (TE) queries that ensure the constraint validation query is only run when necessary.
4. Discover a means to optimize the constraint validation query by having the TE query provide values that can be used in the validation query.
5. Devise and add a serialization strategy to the data integrity (DI) code.

他们详细介绍了如何在 Oracle 中实现这种可以移植到其他 SQL 产品的策略。