Semantic Constraints

tags: #sql/constraints

What are semantic constraints?

Semantic constraints of a database are rules and restrictions applied to the data that go beyond the simple data type and key constraints.

These constraints are based on the semantics of the data and its relationships and reflect the real-world constraints that apply to the data (business rules etc).

Types of Semantic Constraints

Assertions

Assertions are declarative statements that define conditions that the data in the database must satisfy (i.e., they are essential integrity constraints applied to the entire database). They are checked whenever data is modified or inserted, and if the condition is not met, the modification is rejected.

Triggers

Triggers are procedural code that are automatically executed in response to certain events or actions on the database (i.e., they are essentially used to monitor the database for undesired actions).

Creating ASSERTIONS

CREATE ASSERTION assertion_name
CHECK (condition); // condition to be satisfied for the assertion to be true.

The condition specified in an assertion is typically a SELECT statement that defines the constraint that must be satisfied by the database.

Example:

You want to ensure that the total number of employees in the database never exceeds 100:

CREATE ASSERTION max_emp_count
CHECK ( SELECT COUNT(*) FROM employee) <= 100;

Creating TRIGGERS

A trigger is a set of instructions or code that gets executed automatically in response to certain events or changes that occur within a database (e.g., enforcing data constraints, updating data).

A typical trigger has three components:

1. Event(s)
2. Condition
3. Action

CREATE TRIGGER trigger_name

[BEFORE|AFTER|INSTEAD OF] -- when the trigger should activate

[INSERT|UPDATE|DELETE] -- triggering event

ON table_name -- which table to generate trigger on

FOR EACH <ROW | STATEMENT> -- execute for each row affected or entire statement

WHEN (<condition>) -- condition for the trigger

BEGIN
    -- SQL statements to be executed / trigger body / action to be taken
END;

Example:

Checking that the salary being updated is not less than the minimum salary allowed for the employee's job title.

CREATE TRIGGER salary_validation
BEFORE UPDATE ON employee
FOR EACH ROW
WHEN (NEW.salary < (SELECT min_salary FROM job WHERE job_title = NEW.job_title))
BEGIN
  RAISE EXCEPTION 'Salary cannot be less than the minimum for this job title.';
END;