Create Assertion (SQL)

We can create a new assertion using CREATE ASSERTION, which includes a name, a check keyword, and a condition clause. Enforcing the assertion is up to the database implementation, such as rejecting a query that violates it.

CREATE ASSERTION SALARY_CONSTRAINT
CHECK (
	NOT EXISTS (
		SELECT *
		FROM   EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
		WHERE  E.salary > M.salary
		  AND  E.dno=D.number
		  AND  D.mgr_ssn=M.ssn
	)
)

In this example, the salary of an employee must not be greater than the salary of the manager of the department that the employee works for.