For more information on CREATE ASSERTION see SQL SQL-92 Standard Specification.
The definition of CHECK must be in parentheses.
CURRENT_DATE does not have parentheses.
USER and DATE are reserved words.
SQL statements must be terminated with a semicolon.
SQL keywords must be uppercase.
Try something else 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 check the syntax using the online Mimer SQL-92 Validator . However, you should also check your logic, for example. CURRENT_DATE not deterministic.
Also, I don't think this ASSERTION will ever bite. When the power of the subquery is less than 10, it will return zero rows and 0 = empty set will be evaluated to UNKNOWN . When the subquery power is 10 or higher, the search term will evaluate to TRUE . SQL-92 Standard State
An assertion fails if and only if the result of the evaluation of the search condition is incorrect.
Note that you can replace the CHECK (0 = (SELECT COUNT(*) FROM...)) construct with CHECK (NOT EXISTS (SELECT * FROM...)) , the last of which is easier for me to write.
UPDATE:
How to write an expression using CHECK DOES NOT EXIST?
As I said above, your logic seems erroneous, so it is difficult to implement it properly;)
Let's say the rule is to limit the video to 10 per user per day. Since this is associated with only one table, it would be more appropriate to use a CHECK constraint at the table level; this restriction is checked when the table is updated, which is sufficient in this case (there is no reason why it could not be ASSERTION , although theoretically it would be possible to check every time any table in the scheme 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 ));
UPDATE 2:
thank you, now let me say that we want to limit the video to 100 per user per year, in this case using current_date will be well, is not it?
Again, repeat that a CHECK / ASSERTION will only be checked when updating data in a table / chart. The problem with using CURRENT_DATE (and other non-integrated functions) in the restriction is that a business rule can be canceled simply by switching the clock from one period to another, but if the data has not been changed in this period, then a data integrity failure will not be detected , and the database will contain invalid data.
Another consideration is what is meant by one year in context.
This can be a calendar year (from January 1 to December 31 inclusive) or other other fixed dates defined by the company (for example, from April 1 to March 31 inclusive), in which case the grouping by year and user, then the calculation is trivial.
A more interesting case is when the rule limits the count for any 12-month period; extending this to both the past and the future, to avoid the aforementioned "non-deterministic" problem.
Consider the standard approach of using an auxiliary calendar table , containing one row for each day, applicable to the enterprise, expanded into the past and the future only as necessary should contain only a few thousand rows. Each row will have a date as a key with a second column for that date plus one year (and, if necessary, you can fine tune the definition of “year” with one-day drill!). The test should include attaching a table to the calendar, grouping by calendar date and user, and counting, for example 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 can be placed in the CHECK (NOT EXISTS (... constraint CHECK (NOT EXISTS (... It can still be a CHECK constraint at the table level: since the calendar table is an auxiliary table, it will only be suitable for rare controlled updates (but may be ASSERTION if necessary).