An assertion in SQL is a named statement that specifies a requirement that must be true for the database’s contents. The assertion will fail and the database will produce an error if the condition is not satisfied.
The SQL syntax for writing an assertion is as follows:
CREATE ASSERTION assertion_name
CHECK (condition);
For instance, you could use the following sentence to establish an assertion called “salary_assertion” that verifies that no employee in the “employees” table has a salary more than $100,000:
CREATE ASSERTION salary_assertion
CHECK (salary <= 100000);
To drop an assertion, you can use the following statement:
DROP ASSERTION assertion_name;
Assertions can be used to enforce data integrity and guarantee that the database’s data complies with particular requirements. They can be applied to assure data consistency or to enforce business standards. They are not often used in practice though because they can take a lot of effort to make and maintain.
