General Constraints (SQL)

General constraints are those that do not fit in the basic SQL categories.

It is useful for schema assertions: outside of the scope of the built-in relational model constraints (primary / unique keys, entity integrity, referential integrity)

Defines whether the state of the database is valid at any given point in time.

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.

Link to original