这是创建SQL断言的正确方法吗? [英] Is this the correct way to create SQL assertion?

问题描述

以进行以下类型的断言

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

推荐答案

有关CREATE ASSERTION的完整详细信息，请参见ISO SQL-92标准规范.

For full details on CREATE ASSERTION see the ISO SQL-92 Standard spec.

CHECK定义应放在括号中.

The CHECK definition should be in parentheses.

CURRENT_DATE没有括号.

USER和DATE是保留字.

SQL语句应以分号结尾.

SQL statements should be terminated with a semicolon character.

SQL关键字应大写.

SQL Keywords should be in upper case.

尝试更多类似的方法:

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是不确定的.

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_DATE is non-deterministic.

此外，我认为这个ASSERTION不会咬人.当子查询的基数小于10时，它将返回零行，并且0 = empty set将计算为UNKNOWN.当子查询的基数为10或更大时，搜索条件将评估为TRUE. SQL-92标准状态

Also, I don't think this ASSERTION will ever bite. When the cardinality of the subquery is less than 10 it will return zero rows and 0 = empty set will evaluate to UNKNOWN. When the cardinality of the subquery is 10 or greater then the search condition will evaluate TRUE. SQL-92 Standard states

如果满足，则断言不满足 仅当评估结果为 搜索条件为假.

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...))，我发现后者更容易编写.

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.

更新:

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

How should I write the assertion using CHECK NOT EXISTS ?

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

As I said above, your logic appears flawed so it is hard to implement properly ;)

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

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 CHECK constraint; 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 any table in the schema is updated):

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:

---

UPDATE 2:

谢谢，现在我们要限制 影片，每位使用者每年100部影片， 这种情况下使用current_date将是 是不是有必要?

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(和其他非确定性函数)的问题是，可以简单地通过从一个时间段到下一个时间段的时钟滴答来使业务规则无效，但是如果该时间段中的数据没有更改，在此期间，将不会检测到数据完整性故障，并且数据库将包含无效数据.

Consider again that a CHECK/ASSERTION will 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.

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

Another consideration is what is meant by a year in context.

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

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.

一个更有趣的情况是，该规则限制了 any 12个月的累计量；将其扩展到过去和将来可以避免上述不确定性"问题.

A more interesting case is when the rule limits the tally for any 12 month period; extending this to both the past and future avoid the above 'non-deterministic' issue.

请考虑使用辅助日历表的标准方法，每天包含一行适用于企业，仅根据需要扩展到过去和将来，仍应仅包含几千行.每行将日期作为关键字，该日期的第二列加上一年(如果需要，您可以按一天的间隔对一年"的定义进行微调！)的测试将涉及加入日历表格，按日历日期和用户分组并进行计数像这样的东西:

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;

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

This could be put in a CHECK (NOT EXISTS (... constraint. This could still be a table-level CHECK constraint: because the Calendar table is an auxiliary table it would only be suject to infrequent controlled updates (but again could be an ASSERTION if required).

这篇关于这是创建SQL断言的正确方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！