Global Consistency with Schema-Level Assertions

Snippet

Global Consistency with Schema-Level Assertions

Assertions are powerful schema-level constraints that can validate logic across multiple tables. Unlike triggers, they are declarative and ensure the database never enters an invalid state, regardless of the operation performed.

snippet.sql
sql
CREATE ASSERTION balance_safety 
CHECK (NOT EXISTS (
  SELECT 1 FROM accounts 
  WHERE type = 'SAVINGS' AND balance < 0
));

Breakdown

CREATE ASSERTION

Defines a global constraint independent of a single table's lifecycle.

CHECK (NOT EXISTS (...))

The logic that must always evaluate to true for every transaction commit.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Global Consistency with Schema-Level Assertions

Related