Integrity and Domain Constraints

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

Integrity and Domain Constraints

Rules the database enforces so that invalid data cannot exist — not merely "should not".

🌍 Where this lives: the argument "we validate in the application, so we don't need database constraints" fails the moment a second application appears — or a data-migration script, or an admin fixing something by hand at midnight, or a background job written by someone who left. Every one of those bypasses your validation layer. A CHECK (balance >= 0) cannot be bypassed by anything, ever. That is why banking schemas are constraint-heavy and why "we found 40,000 rows with impossible values" is a normal outcome of auditing a constraint-light database. Search "database constraints vs application validation"; the consensus is both, never one.

The five constraint categories

1. DOMAIN CONSTRAINTS restrict the set of values an attribute may take · data type INTEGER, DATE, VARCHAR(60) · NOT NULL · CHECK on one column · DEFAULT · a user-defined DOMAIN or ENUM type 2. ENTITY INTEGRITY the primary key must be unique and NOT NULL · no two tuples share a primary key value · no part of a primary key may be NULL Rationale: a row you cannot identify is not a fact about anything. 3. REFERENTIAL INTEGRITY a foreign key value must exist in the referenced relation, or be entirely NULL · prevents "dangling references" · governed by ON DELETE / ON UPDATE actions 4. KEY CONSTRAINTS candidate keys other than the primary one · UNIQUE 5. SEMANTIC / BUSINESS-RULE CONSTRAINTS application-specific rules that are not structural · CHECK spanning several columns · ASSERTION (spanning several tables — see below) · TRIGGER for anything more complex DECLARATIVE vs PROCEDURAL — an important distinction: DECLARATIVE you state WHAT must be true; the DBMS decides how to enforce it PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY, NOT NULL → optimiser can use them, always enforced PROCEDURAL you write code that runs on each change TRIGGER, stored procedure → more powerful, but slower and easier to get wrong RULE: prefer declarative. Reach for a trigger only when no declarative form can express the rule.

constraints.sql

-- ===== named constraints: always name them, so error
-- messages and ALTER statements are readable =====
CREATE TABLE department (
    code   CHAR(6)       CONSTRAINT pk_dept PRIMARY KEY,
    name   VARCHAR(60)   CONSTRAINT uq_dept_name UNIQUE
                         CONSTRAINT nn_dept_name NOT NULL,
    budget NUMERIC(12,2) CONSTRAINT chk_budget
                         CHECK (budget >= 0)
);

CREATE TABLE student (
    roll        INTEGER      CONSTRAINT pk_student PRIMARY KEY,
    name        VARCHAR(60)  NOT NULL,
    email       VARCHAR(120) CONSTRAINT uq_email UNIQUE,
    dob         DATE         NOT NULL,
    admitted_on DATE         NOT NULL DEFAULT CURRENT_DATE,
    marks       NUMERIC(5,2),
    dept_code   CHAR(6)      NOT NULL,

    -- DOMAIN constraint: single column
    CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100),

    -- SEMANTIC constraint: spans two columns of this row
    CONSTRAINT chk_dates CHECK (admitted_on > dob),

    -- another multi-column rule
    CONSTRAINT chk_age CHECK (
        admitted_on - dob >= 365 * 15   -- at least ~15 yrs
    ),

    -- pattern constraint
    CONSTRAINT chk_email CHECK (email IS NULL
                                OR email LIKE '%_@_%.__%'),

    -- REFERENTIAL integrity
    CONSTRAINT fk_dept FOREIGN KEY (dept_code)
        REFERENCES department(code)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

INSERT INTO department VALUES ('ACtE07','Computer Engineering',5000000);

-- ===== each constraint rejects its own violation ===== 
INSERT INTO student (roll,name,email,dob,marks,dept_code)
    VALUES (101,'Ram','ram@x.com','2004-03-15',87.5,'ACtE07');
-- ✔ accepted

INSERT INTO student (roll,name,dob,marks,dept_code)
    VALUES (101,'Duplicate','2004-01-01',50,'ACtE07');
-- ERROR: duplicate key value violates unique constraint
--        "pk_student"                    -> ENTITY INTEGRITY

INSERT INTO student (roll,name,dob,marks,dept_code)
    VALUES (102,'Bad Marks','2004-01-01',150,'ACtE07');
-- ERROR: new row for relation "student" violates check
--        constraint "chk_marks"          -> DOMAIN

INSERT INTO student (roll,name,dob,admitted_on,marks,dept_code)
    VALUES (103,'Time Travel','2004-01-01','2003-01-01',70,'ACtE07');
-- ERROR: violates check constraint "chk_dates"
--                                        -> SEMANTIC

INSERT INTO student (roll,name,dob,marks,dept_code)
    VALUES (104,'Ghost','2004-01-01',70,'XXXXXX');
-- ERROR: violates foreign key constraint "fk_dept"
--                                        -> REFERENTIAL

INSERT INTO student (roll,name,email,dob,marks,dept_code)
    VALUES (105,'Dup Email','ram@x.com','2004-01-01',70,'ACtE07');
-- ERROR: duplicate key value violates unique constraint
--        "uq_email"                      -> KEY

Six inserts, one accepted and five rejected — each by a DIFFERENT constraint category. That is the whole topic in one listing. WHY NAME CONSTRAINTS: compare the two error messages unnamed: violates check constraint "student_marks_check" named: violates check constraint "chk_marks" The generated name is derivable but unstable — it changes if you reorder columns, and it differs between database systems. A named constraint can also be dropped and re-added by name: ALTER TABLE student DROP CONSTRAINT chk_marks; ALTER TABLE student ADD CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100); Application code that maps constraint names to user-facing messages ("Marks must be between 0 and 100") depends on this stability. NOTE the chk_email pattern '%_@_%.__%' reads as: at least one char, then @, then at least one char, then a dot, then at least two chars It is deliberately loose. Full email validation by regex is a known trap — the real specification permits far more than people expect — so a loose sanity check plus a confirmation email is the correct approach.

CHECK constraints and their limits

A CHECK constraint may reference only columns of the SAME ROW. That single restriction defines what it can and cannot do. ✔ CHECK (marks BETWEEN 0 AND 100) ✔ CHECK (end_date > start_date) ✔ CHECK (discount <= price * 0.5) ✔ CHECK (status IN ('active','suspended','closed')) ✔ CHECK ((type = 'student' AND marks IS NOT NULL) OR (type = 'staff' AND salary IS NOT NULL)) ✗ CHECK (marks <= (SELECT max_marks FROM course ...)) → a subquery. Most systems reject it; those that allow it cannot keep it true when the OTHER table changes. ✗ CHECK (COUNT(*) <= 60) → aggregate over other rows. Not permitted. ✗ CHECK (admitted_on <= CURRENT_DATE) → non-deterministic. Accepted by some systems but DANGEROUS: a row valid when inserted becomes invalid tomorrow, and a table reload or a dump/restore then FAILS. THE THREE-VALUED LOGIC RULE — the trap that matters: a CHECK passes if it evaluates to TRUE **or UNKNOWN**. It fails only on FALSE. CHECK (marks BETWEEN 0 AND 100) INSERT ... marks = NULL → NULL BETWEEN 0 AND 100 → UNKNOWN → ACCEPTED ✔ So a CHECK does NOT imply NOT NULL. If marks is mandatory you must write both: marks NUMERIC(5,2) NOT NULL CHECK (marks BETWEEN 0 AND 100) This is the single most common constraint bug: a carefully written CHECK silently permits NULL. FOR RULES A CHECK CANNOT EXPRESS: · another table involved → FOREIGN KEY, or a TRIGGER · a count or aggregate → TRIGGER · rule across the whole table → ASSERTION (rarely implemented) or TRIGGER

Worked example — the domain type and the ENUM

domains.sql

-- A DOMAIN is a named, reusable data type WITH constraints.
-- Define the rule once, apply it to many columns.
CREATE DOMAIN marks_type AS NUMERIC(5,2)
    CONSTRAINT valid_marks CHECK (VALUE BETWEEN 0 AND 100);

CREATE DOMAIN email_type AS VARCHAR(120)
    CONSTRAINT valid_email CHECK (VALUE LIKE '%_@_%.__%');

CREATE DOMAIN nepali_phone AS VARCHAR(15)
    CONSTRAINT valid_phone CHECK (VALUE ~ '^(98|97)[0-9]{8}$');

-- an ENUM restricts to a fixed list, and ORDERS it
CREATE TYPE grade_type AS ENUM
    ('A+','A','B+','B','C+','C','D','F');

CREATE TABLE result (
    roll     INTEGER      NOT NULL,
    course   CHAR(8)      NOT NULL,
    theory   marks_type   NOT NULL,      -- domain reused
    practical marks_type,                -- same rule, no
                                         -- duplication
    grade    grade_type   NOT NULL,
    contact  nepali_phone,
    email    email_type,
    PRIMARY KEY (roll, course)
);

INSERT INTO result VALUES
    (101,'ACtE0703',68.5,22.0,'A','9841000001','ram@x.com');
-- ✔ accepted

INSERT INTO result VALUES
    (102,'ACtE0703',150,20,'A','9841000002','sita@x.com');
-- ERROR: value for domain marks_type violates check
--        constraint "valid_marks"

INSERT INTO result VALUES
    (103,'ACtE0703',70,20,'A','1234567890','hari@x.com');
-- ERROR: value for domain nepali_phone violates check
--        constraint "valid_phone"

INSERT INTO result VALUES
    (104,'ACtE0703',70,20,'Z','9841000004','gita@x.com');
-- ERROR: invalid input value for enum grade_type: "Z"

-- the ENUM's declared order enables meaningful sorting
SELECT roll, grade FROM result ORDER BY grade;
-- sorts A+ before A before B+ ... not alphabetically

-- CHANGING A DOMAIN updates every column using it: 
ALTER DOMAIN marks_type DROP CONSTRAINT valid_marks;
ALTER DOMAIN marks_type ADD CONSTRAINT valid_marks
    CHECK (VALUE BETWEEN 0 AND 100 AND VALUE = ROUND(VALUE,1));
-- both theory and practical now require one decimal place —
-- one ALTER, two columns. That is the value of a domain.

Assertions and the multi-table problem

The SQL standard defines an ASSERTION for constraints spanning several tables: CREATE ASSERTION total_budget CHECK ( (SELECT SUM(budget) FROM department) <= 50000000 ); CREATE ASSERTION section_capacity CHECK ( NOT EXISTS ( SELECT 1 FROM section s WHERE (SELECT COUNT(*) FROM enrolment e WHERE e.section_id = s.id) > s.capacity ) ); THE PRACTICAL REALITY: almost no production DBMS implements CREATE ASSERTION — not PostgreSQL, not MySQL, not SQL Server, not Oracle. WHY NOT: an assertion is a database-wide invariant, so the engine would have to re-evaluate it after EVERY statement on EVERY table it mentions, with no way to know which changes could affect it. The cost is unbounded. WHAT TO USE INSTEAD — a trigger on each affected table:

assertion_via_trigger.sql

CREATE TABLE section (
    id       SERIAL PRIMARY KEY,
    course   CHAR(8)  NOT NULL,
    capacity SMALLINT NOT NULL CHECK (capacity > 0)
);
CREATE TABLE enrolment (
    roll       INTEGER NOT NULL,
    section_id INTEGER NOT NULL REFERENCES section(id),
    PRIMARY KEY (roll, section_id)
);

-- The rule "enrolments must not exceed capacity" spans two
-- tables and involves a COUNT, so no CHECK can express it.
CREATE OR REPLACE FUNCTION enforce_capacity()
RETURNS TRIGGER AS $$
DECLARE
    used INTEGER;
    cap  INTEGER;
BEGIN
    SELECT capacity INTO cap
      FROM section WHERE id = NEW.section_id
      FOR UPDATE;                  -- lock to avoid a race

    SELECT COUNT(*) INTO used
      FROM enrolment WHERE section_id = NEW.section_id;

    IF used >= cap THEN
        RAISE EXCEPTION
          'section % is full (capacity %)', NEW.section_id, cap;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_capacity
    BEFORE INSERT ON enrolment
    FOR EACH ROW EXECUTE FUNCTION enforce_capacity();

INSERT INTO section (course, capacity) VALUES ('ACtE0703', 2);
INSERT INTO enrolment VALUES (101, 1);   -- ✔ 1 of 2
INSERT INTO enrolment VALUES (102, 1);   -- ✔ 2 of 2
INSERT INTO enrolment VALUES (103, 1);
-- ERROR: section 1 is full (capacity 2)

-- NOTE the FOR UPDATE lock. Without it, two concurrent
-- transactions could both read used = 1, both decide there
-- is room, and both insert — leaving 3 enrolments in a
-- 2-seat section. That race is the reason trigger-based
-- constraints are harder than they look, and why declarative
-- constraints are preferable whenever they suffice.

THE RACE CONDITION, spelled out, because it is what separates a working trigger from a broken one: time transaction A transaction B ──────────────────────────────────────────────────────── t1 SELECT COUNT → 1 t2 SELECT COUNT → 1 t3 1 < 2, so proceed t4 1 < 2, so proceed t5 INSERT (total now 2) t6 INSERT (total now 3) ✗ Both transactions read the same count before either wrote. The constraint is violated even though each transaction checked it correctly. THREE FIXES, in order of preference: 1. SELECT ... FOR UPDATE on the section row (as above) — serialises the check by locking the parent 2. SERIALIZABLE isolation — the DBMS detects the conflict and aborts one transaction 3. store a counter column on section and use an atomic UPDATE ... SET used = used + 1 WHERE used < capacity, then check the affected row count This is exactly why "just use a trigger" is not equivalent to a declarative constraint: declarative constraints are implemented inside the engine with the correct locking already handled.

The lesson generalises past databases: any check-then-act sequence across a boundary is a race unless something serialises it. It is the same bug as checking a file exists before opening it, or testing a balance before withdrawing. Declarative constraints are valuable precisely because the engine does the serialisation for you.

Deferred constraints and circular references

Some constraints cannot be satisfied at the moment of a single statement. department.head_id → employee.id employee.dept_code → department.code To insert the first department you need an employee. To insert the first employee you need a department. Deadlock by design. SOLUTION 1 — nullable column, two steps: INSERT INTO department (code,name) VALUES ('ACtE07','CE'); INSERT INTO employee VALUES (1,'Sharma','ACtE07'); UPDATE department SET head_id = 1 WHERE code = 'ACtE07'; SOLUTION 2 — DEFERRABLE constraint, checked at COMMIT: ALTER TABLE department ADD CONSTRAINT fk_head FOREIGN KEY (head_id) REFERENCES employee(id) DEFERRABLE INITIALLY DEFERRED; BEGIN; INSERT INTO department VALUES ('ACtE07','CE',1); INSERT INTO employee VALUES (1,'Sharma','ACtE07'); COMMIT; -- BOTH constraints checked HERE, and both hold The intermediate state violates the constraint, and that is permitted because the constraint is only required to hold at transaction boundaries. TIMING OPTIONS: NOT DEFERRABLE (default) checked per statement DEFERRABLE INITIALLY IMMEDIATE checked per statement, but can be deferred with SET CONSTRAINTS DEFERRABLE INITIALLY DEFERRED checked at COMMIT Note: PRIMARY KEY and UNIQUE can be deferrable in PostgreSQL, which permits temporarily swapping two rows' unique values inside one transaction — otherwise impossible without a placeholder value.

🌍 Go further: PostgreSQL has a constraint type most courses never mention and which is genuinely powerful: EXCLUDE. It generalises UNIQUE from equality to any operator, so EXCLUDE USING gist (room WITH =, during WITH &&) declaratively prevents two bookings of the same room with overlapping time ranges — a rule that otherwise needs a trigger and careful locking. Combined with range types it eliminates a whole family of scheduling bugs. Search "PostgreSQL exclusion constraint overlapping ranges".

💡 Exam angle: name the five constraint categories — domain, entity, referential, key, and semantic/business — with an example of each. State that entity integrity means the primary key is unique and non-NULL, and referential integrity means every foreign key value exists or is NULL. Two high-value details: a CHECK passes on UNKNOWN, so it does not imply NOT NULL; and ASSERTION is in the standard but essentially unimplemented, so multi-table rules use triggers. Mention deferred constraints for circular references.

Syllabus points

Entity/referential integrity; domain constraints

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.