sql - 这是创建SQL断言的正确方法吗？

Question

为了做出以下类型的断言

create assertion assert  
check "EMPTY SET" = (select User         
     from Video  
     where date=current_date()  
     group by user  
having count(*) >= 10

这个主张正确吗？

create assertion assert  
check  0 = (select count(*)  
     from Video  
     where date=current_date()  
     group by user  
having count(*) >= 10

Answer 1

有关CREATE ASSERTION的完整详细信息，请参见ISO SQL-92标准规范。
CHECK定义应放在括号中。
CURRENT_DATE没有括号。
USER和DATE是保留字。

SQL语句应以分号字符终止。

SQL关键字应为大写。

尝试类似这样的方法:

CREATE ASSERTION assert  
CHECK (0 = (
            SELECT COUNT(*)  
              FROM Video  
             WHERE my_date = CURRENT_DATE  
             GROUP 
                BY my_user
            HAVING COUNT(*) >= 10
           ));

您可以使用在线Mimer SQL-92 Validator来测试语法是否正确。但是，您还应该测试自己的逻辑，例如 CURRENT_DATE是不确定的。

另外，我不认为此 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.

请注意，您可以将 CHECK (0 = (SELECT COUNT(*) FROM...))构造替换为 CHECK (NOT EXISTS (SELECT * FROM...))，我发现后者更易于编写。

更新:

How should I write the assertion using CHECK NOT EXISTS ?

如上所述，您的逻辑似乎有缺陷，因此很难正确实现;)

假设规则是将视频限制为每位用户每天10个视频。因为这仅涉及单个表，所以使用表级的 CHECK约束更为合适；在表更新时检查了这种约束，这种约束在这种情况下就足够了(尽管没有理由说它不能是 ASSERTION，但理论上每次更新Schemat中的任何表时都可以检查该约束):

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
                        ));

更新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?

再次考虑到 CHECK/ ASSERTION仅在更新表/架构中的数据时才进行检查。在约束中使用 CURRENT_DATE(和其他非确定性函数)的问题是，可以通过从一个时间段到下一个时间段的时钟滴答作响，简单地使业务规则无效，但是如果该时间段内的数据没有更改，那么则不会检测到数据完整性故障，并且数据库将包含无效数据。

另一个考虑因素是上下文中一年的含义。

它可以是日历年(包括1月1日至12月31日)或企业定义的其他其他固定日期(例如，包括4月1日至3月31日)，在这种情况下，按年份和用户分组然后进行计数是微不足道的。

更有意思的情况是，该规则限制了任何12个月内的累计次数；将其扩展到过去和将来可以避免上述“不确定性”问题。

考虑一个 standard approach of using an auxiliary calendar table，它每天包含一行，适用于企业，并且仅在需要时才扩展到过去和将来，该行仅应包含几千行。每行将日期作为关键字，该日期的第二列加上一年(如果需要，您可以按一天的间隔对“一年”的定义进行微调!)的测试将涉及加入日历表格，按日历日期和用户分组并进行计数像这样的东西:

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;

这可以放在 CHECK (NOT EXISTS (...约束中。这仍然可能是表级的 CHECK约束:由于Calendar表是辅助表，因此只会受到不频繁的受控更新(但如果需要，也可以是 ASSERTION)。