这是创建 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 测试语法是否正确验证器。但是，您还应该测试您的逻辑，例如 CURRENT_DATE 是不确定的。

另外，我不认为这个 ASSERTION 会起作用。当子查询的基数小于 10 时，它将返回零行，并且 0 = 空集 将计算为 UNKNOWN。当子查询的基数为 10 或更大时，搜索条件将评估 TRUE。 SQL-92 标准状态

断言不满足如果并且
仅当评估结果
搜索条件不正确。

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

---

更新：

我应该如何使用编写断言
检查不存在？

正如我上面所说，您的逻辑似乎有缺陷，因此很难正确实施；）

假设规则是将每个用户每天的视频限制为 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
                        ));

---

更新2：

谢谢，现在假设我们要限制
每个用户每年观看 100 个视频
在这种情况下使用 current_date 是
有必要不是吗？

再次考虑一下，仅当表/架构中的数据更新时才会检查CHECK/ASSERTION。在约束中使用 CURRENT_DATE（和其他非确定性函数）的问题在于，业务规则可能会因时钟从一个时间段到下一个时间段的滴答而失效，但如果数据没有“如果在此期间没有更改，则不会检测到数据完整性故障，并且数据库将包含无效数据。

另一个考虑因素是一年的含义。

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

一个更有趣的情况是，规则限制任何 12 个月期间的计数；将其扩展到过去和未来可以避免上述“非确定性”问题。

考虑使用辅助日历表的标准方法，包含适用于企业的每一天，扩展到仅根据需要，过去和未来仍应仅包含几千行。每行都将日期作为键，第二列表示该日期加上一年（如果需要，您可以以一天的粒度微调“一年”的定义！）测试将涉及加入日历表，根据日历日期和用户进行分组并进行计数，例如：

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 约束：因为日历表是一个辅助表，所以它只会受到不频繁的受控更新的影响（但如果需要，也可以是一个 ASSERTION）。