为什么不是DBMS的支持ASSERTION [英] Why don't DBMS's support ASSERTION
问题描述
所以我最近在我的数据库课程中学到了ASSERTION，我的prof指出，主要的数据库不支持它，即使它是在SQL-92标准。我尝试谷歌搜索为什么，但似乎没有任何讨论的话题。
So I recently learned about ASSERTION in my databases course, and my prof noted that major databases don't support it, even though it is in the SQL-92 standard. I tried googling to find out why, but there doesn't seem to be any discussion on the topic.
所以，为什么不是ASSERTION支持绝大多数关系数据库包？它是soley的性能问题还是有一些本质上很难的东西？
So, why isn't ASSERTION supported by the vast majority of relational database packages? Is it soley a performance issue or is there something intrinsically hard about it?
如果可以，请注意任何数据库包它实现它（例如：如果有一个学术/教学数据库）。此外，为什么对这个问题几乎没有讨论;它甚至没有在SQL或SQL-92的维基百科页面中提到）但回答主要问题，或在评论中回答。
If you can, please note any database packages that implement it as well (example: if there's an academic/teaching DB). Also, why is there so little discussion on the issue; it's not even mentioned in the Wikipedia page for SQL or SQL-92) But answer the main question first, or answer in comments.
我 / em>
I'm not looking for how to implement it with triggers or anything else.
推荐答案
有四个约束级别：column-level ，行级，表级和模式级。
There are four levels of constraint: column-level, row-level, table-level and schema-level.
例如，表级可能涉及除了声明的源表之外的目标表，但只有在源表更改时才会检查。理论上，将对模式中每个表中的每个更改检查模式级约束，但在实践中，优化器将能够以更精细的方式检测更改;因此，如果您的DBMS支持模式级约束，那么在实践中您将不会对表级约束有太多的使用。
A table-level could, for example, involve a target table other than the source table on which it was declared but only gets checked when the source table changes. In theory a schema-level constraint would be checked for every change in every table in the schema but in practise the optimizer would be able to detect changes in a more granular way; consequently, if your DBMS had support for schema-level constraint then you wouldn't find much use for table-level constraints in practise.
没有当前的SQL产品支持schema-级别约束即 CREATE ASSERTION 。显然， Rdb 在由DEC管理的时候支持它，但情况并非如此。 - 更新：在一个私人消息我被告知，Sybase的SQL Anywhere支持 CREATE ASSERTION ，但有严重的错误，允许这样的约束有时被违反！
No current SQL product supports schema-level constraints i.e. CREATE ASSERTION. Apparently Rdb did support it when it was looked after by DEC but that is no longer the case. -- UPDATE: in a private message I was advised that Sybase's SQL Anywhere supports CREATE ASSERTION but with serious errors that allow such constraints sometimes to be violated!
我使用的唯一的类似于SQL的产品目前支持 CHECK 约束中的子查询，它支持表级约束，是Access数据库引擎（ACE，Jet，无论什么）。 但它有问题。首先，不支持SQL-92功能（或等效的）推迟约束检查。第二，对受影响的每一行检查表级约束，而不是当语句按照SQL-92标准的要求完成时。不用说，解决方法非常笨重。删除约束，并在这样做锁定表，执行更新，重新创建约束。模式级约束，可以通过向它涉及的所有表添加相同的约束来实现，实际上是不可行的。
The only SQL-like product I've used that currently supports subqueries in CHECK constraints, which enables table-level constraints, is the Access database engine (ACE, Jet, whatever). It has issues, though. First, there is not support for SQL-92 functionality (or equivalent) to defer constraint checking. Second, table-level constraints are checked for each row affected, rather than when the statement completes as required by the SQL-92 Standard. Needless to say, the workaround is very clunky e.g. drop the constraint and in doing so lock the table, execute the update, recreate the constraint. Schema-level constraints, arguably achievable by adding the same constraint to all the tables it involves, is virtually unworkable.
可能由于这些原因，Access团队从未公布过 CHECK 约束功能超出 Jet 4.0的初始公告（例如，它仍然缺少Access帮助） 。所有这一切，对于表内约束（例如，有效状态的历史时间表中的顺序键）功能工作得很好，特别是当你认为Access只获得类似触发器的功能（不是基于SQL）年。
Possibly for these reasons, the Access Team have never publicized its CHECK constraint functionality at all beyond the initial announcements for Jet 4.0 (it remains missing from the Access Help, for example). All that said, for intra-table constraints (e.g. a sequenced key in a valid-state 'history' temporal table) the functionality works well, especially when you consider that Access only got trigger-like functionality (not SQL based, though) last year.
SQL当然有 UNIQUE 约束和参照完整性约束，它们当然是表级，但是特殊情况。因此，你将遇到的在野外的所有约束将是colum或行级别。
SQL of course has UNIQUE constraints and referential integrity constraints that are of course table-level but these are special cases. Therefore, all constraints you will encounter 'in the wild' will be either colum- or row-level.
请注意MySQL，尽管在SQL DDL中使用 CHECK（）没有效果。用户如何容忍一个没有 CHECK 约束的SQL产品是超越我的！ PostgreSQL有一个很好的约束模型，提示提示：）
Do be aware with MySQL that, although using CHECK() in SQL DDL will parse without error, it will have no effect. How users can tolerate a SQL product with no CHECK constraints at all is beyond me! PostgreSQL has a excellent constraints model, hint hint :)
为什么表间约束这么罕见支持？一个原因必须是由于历史情况。由于@gbn正确地标识了（在标题并发），Sybase / SQL Server SQL实现的系列是基于一个模型，不能处理表间约束检查，这不是可能会改变的东西。
So why are inter-table constraints so rarelt supported? One reason must be due to historical circumstances. As @gbn correctly identifies (under the title Concurrency), the Sybase/SQL Server family of SQL implementations is based on a model that cannot cope with inter-table constraint checking and that's not something that is likely to ever change.
请考虑另一种方法：如果你今天创建一个SQL产品，你会包括 CREATE ASSERTION ？如果你这样做，你肯定还必须实现 DEFERRABLE 约束（即使多重赋值可以说是更好的模型）。但是，如果你沿着建立一个传统的优化器的路线，你将能够吸取更多的研究和经验。也许你会发现没有商业需求的模式级约束（如果MySQL可以得到反正没有CHECK约束...）如果PostgreSQL不这样做，我不认为任何人都会。
Consider looking at this the other way around: if you were creating a SQL product today, would you include CREATE ASSERTION? If you did, you would certainly have to also implement DEFERRABLE constraints (even though multiple assignment is arguably the better model). But you would be able to draw on a lot more research and experience if you went down the route of building a 'traditional' optimizer. And perhaps you'd find there is no commercial demand for schema-level constraints (if MySQL can get anyway without CHECK constraints...) If PostgreSQL doesn't do it, I don't think anyone ever will.
我认为真正的显示阻止是，大多数工业级产品已经开发了触发功能，允许用户编写任意复杂的约束（加上可以更多，例如发送电子邮件告诉事情发生）。当然，他们是程序性而不是声明性的，编码器必须做很多额外的工作，系统将照顾真正的约束，性能往往不是那么大。但事实是，他们现在存在于真正的产品，并为供应商提供一个免费卡免费卡。如果客户没有为他们敲击表，他们为什么要干扰实施值得的功能？
I think the real show stopper is that most industrial-strength products have already developed trigger functionality that allows users to write 'constraints' of arbitrary complexity (plus can a lot more e.g. send an email to tell something happened). Sure, they are procedural rather than declarative, the coder has to do a lot of extra work that the system would take care of with true constraints, and the performance tends to be not so great. But the fact is they existing in real products today and do provide a 'get out of jail free card' card for vendors. Why should they bother implementing worthy features if customers are not banging the table for them?
至于学术/教学语言，因为@Damien_The_Unbeliever正确标识，教程D CONSTRAINT 总是模式级别，因此允许通过定义的任意复杂度的全局约束。如果你想用这种功能设计你自己的DBMS（!!），你应该考虑实现D规范，同时使用现有的SQL DBMS存储，因为 Dataphor 已经完成。
As regards academic/teaching langauges, as @Damien_The_Unbeliever correctly identifies, a Tutorial D CONSTRAINT are always 'schema'-level, hence allow for global constraints of arbitrary conplexity by definition. If you are looking to design your own DBMS(!!) with this kind of functionality, you should consider implementing the D specification while using an existing SQL DBMS for storage, as Dataphor have done.
一个问题困扰着我： 工业实力SQL DBMS支持触发器，为什么他们不只是将声明性 CREATE ASSERTION 映射到覆盖下的触发器？我早就怀疑，答案是因为他们知道，考虑到他们的传统技术，性能会令人震惊。
A question has been bothering me: given that existing 'industrial strength' SQL DBMSs support triggers, why don't they simply map declarative CREATE ASSERTION to a trigger under the covers? I've long suspected the answer is because they know that performance would be appalling given their legacy technology.
数据库专业应用数学，Toon Koppelaars ，第11章。它们定义了在使用触发器强制执行多元组约束时要使用的各种执行模型。最复杂的（尽管仍然是高度可行的）模型，他们称为EM6，包括以下步骤：
A more satisfying answer is provided in Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars, chapter 11. They define various execution models to use when using triggers to enforce multi-tuple constraints. The most sophisticated (though still highly doable) model, which they call EM6, involves the following steps:
- 将形式规范转换为约束验证查询。
- 开发代码以维护转换效果。
- 设计转换效果（TE）查询，确保约束验证查询只在必要时运行[例如我可以将检查仅限于更新的行吗？ DELETE是否可以违反此约束？是否只有特定的列，UPDATE必须涉及到要求检查约束？ etc]
- 通过让TE查询提供可用于验证查询的值来优化约束验证查询。
- 设计并向数据完整性（DI）代码添加串行化策略。 [即解决并发问题，其中事务不能读取另一个事务正在写的坏数据]。
- Translate the formal specification into a constraint validation query.
- Develop code to maintain transition effects.
- Devise transition effect (TE) queries that ensure the constraint validation query is only run when necessary [e.g. Can I limit the check to only the updated rows? Can a DELETE ever violate this constraint? Are there only specific columns that an UPDATE must involve to require the constraint to be checked? etc]
- Discover a means to optimize the constraint validation query by having the TE query provide values that can be used in the validation query.
- Devise and add a serialization strategy to the data integrity (DI) code. [i.e. solve the concurrecy problem where a transaction cannot read the 'bad' data another transaction is writing].
！）：
因为我们认为DBMS供应商不能
编程接受任意复杂的算法谓词和
然后计算高效的过渡效应（TE）查询，最小
验证查询和最佳序列化代码实现
执行模型EM6，我们不应该期望完全支持多元组
在实际，可用和可接受的方式的约束 - 从这些
供应商在未来。我们最好的希望是，数据库
研究者首先想出更多的常见类约束，
为这些开发方便的shorthands。 DBMS供应商在
中应该为我们提供与这些shorthands一致的新的声明结构
，以便容易地将这些常见的
约束定义到DBMS。给定这样一个公共类声明，
DBMS供应商应该能够编程一个算法，提供
us一个类似EM6的执行模型在覆盖下实现
约束。
Because we believe that it is not possible for a DBMS vendor to program an algorithm that accepts an arbitrarily complex predicate and then computes efficient transition effect (TE) queries, a minimal validation query, and optimal serialization code to implement execution model EM6, we should not expect full support for multi-tuple constraints—in a practical, usable and acceptable way—from these vendors in the future. The best we can hope for is that database researchers first come up with more common classes of constraints and develop convenient shorthands for these. The DBMS vendors, in their turn, should then provide us with new declarative constructs, consistent with these shorthands, to state these common classes of constraints easily to the DBMS. Given such a common class declaration, the DBMS vendor should be able to program an algorithm that provides us with an EM6-like execution model under the covers to implement the constraint.
当然，数据库约束的一个常见类是外键，它已被广泛实现。
One such common class of database constraint is a foreign key, which is already widely implemented, of course.
这篇关于为什么不是DBMS的支持ASSERTION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
