这是创建 SQL 断言的正确方法吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4913327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is this the correct way to create SQL assertion?
提问by user591931
in order to make the following type of assertion
为了做出以下类型的断言
create assertion assert
check "EMPTY SET" = (select User
from Video
where date=current_date()
group by user
having count(*) >= 10
is this assertion right?
这个说法对吗？
create assertion assert
check 0 = (select count(*)
from Video
where date=current_date()
group by user
having count(*) >= 10
回答by onedaywhen
For full details on CREATE ASSERTIONsee the ISO SQL-92 Standard spec.
有关详细信息，CREATE ASSERTION请参阅 ISO SQL-92 标准规范。
The CHECKdefinition should be in parentheses.
该CHECK定义应该是在括号中。
CURRENT_DATEdoes not have parentheses.
CURRENT_DATE没有括号。
USERand DATEare reserved words.
USER和DATE是保留字。
SQL statements should be terminated with a semicolon character.
SQL 语句应该以分号字符结束。
SQL Keywords should be in upper case.
SQL 关键字应为大写。
Try something more like this:
尝试更像这样的事情：
CREATE ASSERTION assert
CHECK (0 = (
SELECT COUNT(*)
FROM Video
WHERE my_date = CURRENT_DATE
GROUP
BY my_user
HAVING COUNT(*) >= 10
));
You can test that the syntax is correct using the online Mimer SQL-92 Validator. However, you should also test your logic e.g. CURRENT_DATEis non-deterministic.
您可以使用在线 Mimer SQL-92 Validator测试语法是否正确。但是，您还应该测试您的逻辑，例如CURRENT_DATE是非确定性的。
Also, I don't think this ASSERTIONwill ever bite. When the cardinality of the subquery is less than 10 it will return zero rows and 0 = empty setwill evaluate to UNKNOWN. When the cardinality of the subquery is 10 or greater then the search condition will evaluate TRUE. SQL-92 Standard states
另外，我不认为这ASSERTION会咬人。当子查询的基数小于 10 时，它将返回零行并0 = empty set计算为UNKNOWN。当子查询的基数为 10 或更大时，将评估搜索条件TRUE。SQL-92 标准状态
The assertion is not satisfied if and only if the result of evaluating the search condition is false.
当且仅当评估搜索条件的结果为假时，断言不成立。
Note you can replace your CHECK (0 = (SELECT COUNT(*) FROM...))construct with CHECK (NOT EXISTS (SELECT * FROM...)), the latter of which I find easier to write.
请注意，您可以用 替换您的CHECK (0 = (SELECT COUNT(*) FROM...))构造CHECK (NOT EXISTS (SELECT * FROM...))，我发现后者更易于编写。
UPDATE:
更新：
How should I write the assertion using CHECK NOT EXISTS ?
我应该如何使用 CHECK NOT EXISTS 编写断言？
As I said above, your logic appears flawed so it is hard to implement properly ;)
正如我上面所说，您的逻辑似乎有缺陷，因此很难正确实施；)
Let's say the rule is to limit Videos to 10 per user per day. Because this involves only a single table, it would be more appropriate to use a table-level CHECKconstraint; such a constraint is checked when the table is updated which is sufficient in this case (there's no reason why it couldn't be an ASSERTION, though, which in theory could be checked each time anytable in the schema is updated):
假设规则是将视频限制为每个用户每天 10 个。因为这只涉及单个表，所以使用表级CHECK约束会更合适；在更新表时检查这样的约束，这在这种情况下就足够了（没有理由为什么它不能是ASSERTION，但理论上可以在每次更新架构中的任何表时检查）：
ALTER TABLE Video ADD
CONSTRAINT video_limit_10_per_user_per_day
CHECK (NOT EXISTS (
SELECT v1.my_user, v1.my_date
FROM Video AS V1
GROUP BY v1.my_user, v1.my_date
HAVING COUNT(*) > 10
));
UPDATE 2:
更新 2：
thanks,now let's say we want to limit videos to 100 per user per year, in this case using current_date would be necessary wouldn't it?
谢谢，现在假设我们希望将视频限制为每个用户每年 100 个，在这种情况下使用 current_date 是必要的，不是吗？
Consider again that a CHECK/ASSERTIONwill only be checked when data in the table/schema is updated. The problem with using CURRENT_DATE(and other non-determninistic functions) in a constraint is that the business rule can be invalidated simply by the clock ticking over from one time period to the next but if the data hasn't been changed in that period then the data integrity failure will not be detected and the database will contain invalid data.
再考虑到一CHECK/ASSERTION时，在表/架构数据更新将只进行检查。CURRENT_DATE在约束中使用（和其他非确定性函数）的问题是，业务规则可以简单地通过时钟从一个时间段滴答到下一个时间段而失效，但是如果数据在那个时间段内没有改变，那么不会检测到数据完整性故障，并且数据库将包含无效数据。
Another consideration is what is meant by a year in context.
另一个考虑因素是上下文中的一年的含义。
It could be the calendar year (1 Jan to 31 Dec inclusive) or other other fixed dates defined by enterprise (e.g. 1 Apr to 31 Mar inclusive), in which case grouping by year and user then counting is trivial.
它可以是日历年（包括 1 月 1 日至 12 月 31 日）或其他企业定义的其他固定日期（例如，4 月 1 日至 3 月 31 日），在这种情况下，按年份和用户分组然后计数是微不足道的。
A more interesting case is when the rule limits the tally for any12 month period; extending this to both the past and future avoid the above 'non-deterministic' issue.
一个更有趣的情况是，规则限制了任何12 个月期间的计数；将此扩展到过去和未来可避免上述“非确定性”问题。
Consider a standard approach of using an auxiliary calendar table, containing one row for every day applicable to the enterprise, extended into the past and future only as far as required should still only comprise a few thousand rows. Each row would have the date as a key with a second column for that date plus one year (and if necessary you could fine tune the definition of 'a year' at one-day granularity!) The test for would involve joining to the Calendar table, grouping on the calendar date and user and counting e.g. something like this:
考虑使用辅助日历表的标准方法，适用于企业的每一天包含一行，仅在需要时扩展到过去和未来仍应仅包含几千行。每行都将日期作为键，该日期加上一年的第二列（如果有必要，您可以以一天的粒度微调“一年”的定义！） 测试将涉及加入日历表，按日历日期和用户分组并计数，例如：
SELECT C1.dt, V1.my_user
FROM Video AS V1
INNER JOIN Calendar AS C1
ON (V1.my_date BETWEEN C1.dt AND C1.dt_plus_one_year)
GROUP
BY C1.dt, V1.my_user
HAVING COUNT(*) > 100;
This could be put in a CHECK (NOT EXISTS (...constraint. This could still be a table-level CHECKconstraint: because the Calendar table is an auxiliary table it would only be suject to infrequent controlled updates (but again could be an ASSERTIONif required).
这可以置于CHECK (NOT EXISTS (...约束中。这仍然可能是一个表级CHECK约束：因为 Calendar 表是一个辅助表，它只会受到不频繁的控制更新（但ASSERTION如果需要，也可能是一个）。
