COMP9120 · Database Management Systems
Database Integrity
Database integrity is the COMP9120 topic where a design becomes enforcement: the University of Sydney's Database Management Systems unit teaches you to spell out the rules that keep every stored state legal — domain, key, referential and semantic constraints, plus assertions and triggers — in SQL. On the paper it is examined alongside ER→relational mapping, where correct CREATE TABLE DDL and precise constraint reasoning earn steady method marks.
What this chapter covers
- 01What an integrity constraint is, and the three ways a DBMS reacts to a violation (reject, abort, maintenance action)
- 02Static families (domain, key, referential, semantic, assertions) vs the one dynamic family (triggers)
- 03Domain constraints: data type, DEFAULT, NOT NULL, CHECK, and CREATE DOMAIN
- 04Key constraints: PRIMARY KEY vs UNIQUE, NULL behaviour, and composite keys
- 05Referential integrity: foreign keys, dangling references, and the four on-delete/update actions
- 06Constraint timing: NOT DEFERRABLE (immediate) vs DEFERRABLE (checked at COMMIT)
- 07Assertions across tables, and the for-all → NOT EXISTS rewriting pattern
- 08Why PostgreSQL has no assertions, and the trigger workaround (ECA: Event–Condition–Action)
- 09Trigger timing (BEFORE/AFTER) and granularity (FOR EACH ROW vs FOR EACH STATEMENT)
Enforcing referential integrity with a foreign key
- +1Referential integrity: for every child foreign-key value there must exist a matching parent candidate-key value; otherwise the child row is a dangling reference and is not allowed.
- +2(a) sid = 777 has no matching Student, so the insert would create a dangling reference — it is rejected.
- +2(b) The foreign key says ON DELETE CASCADE, so deleting student 101 also deletes that student's two enrolment rows.
- +1(c) It is a static referential-integrity constraint, checked immediately (NOT DEFERRABLE by default) whenever the child or parent changes.
Key terms
- Integrity constraint (IC)
- A condition that must be true for every legal database instance; on violation the DBMS rejects the operation, aborts the transaction, or runs a maintenance action.
- Domain constraint
- A limit on the values a single attribute may take — its data type, a DEFAULT, NOT NULL, or an attribute-level CHECK such as CHECK (mark BETWEEN 0 AND 100).
- Primary key vs UNIQUE
- PRIMARY KEY marks the chosen key (implicitly NOT NULL, exactly one per table); UNIQUE marks an alternate candidate key (may allow a NULL, any number per table).
- Referential integrity
- Every foreign-key value in a child table must match a candidate-key value in the parent; a value with no match is a dangling reference and is disallowed.
- On-delete/update action
- What happens to child rows when a referenced parent key is deleted or changed: NO ACTION (reject, the default), CASCADE, SET NULL, or SET DEFAULT.
- Assertion
- A schema-level constraint spanning multiple tables, CREATE ASSERTION name CHECK (condition); re-tested on any update that could violate it. Not supported by PostgreSQL.
- For-all → NOT EXISTS
- ∀x P(x) ≡ ¬∃x ¬P(x); an assertion asserts NOT EXISTS (rows that violate P), which holds exactly when that query returns the empty set.
- Trigger (ECA)
- Procedural code run automatically on a data event — Event–Condition–Action; timing BEFORE/AFTER/INSTEAD OF, granularity FOR EACH ROW or FOR EACH STATEMENT. The dynamic constraint family.
Database Integrity FAQ
What is the difference between an assertion and a CHECK constraint?
A table-level CHECK sees only one row of one table and is re-checked only when that table changes, so it cannot express a rule that counts or compares rows across a whole table or across tables. An assertion spans multiple tables and is re-checked on any update that could violate it, which is its advantage; its disadvantages are cost and that major systems (including PostgreSQL) do not implement it, so you enforce the rule with a trigger instead.
Does PostgreSQL support SQL assertions?
No. PostgreSQL does not support CREATE ASSERTION and does not allow subqueries inside a CHECK constraint. The practical workaround, and the one used on PostgreSQL in this unit, is a trigger: an AFTER INSERT/UPDATE trigger whose function tests the condition and raises an exception when it would be violated, rejecting the change.
Can AI help me with database integrity constraints in COMP9120?
Yes, as a study aid. Sia is an AI tutor that explains concepts step by step — it can walk you through why a foreign key rejects a dangling reference, how an ON DELETE CASCADE differs from SET NULL, or how to rewrite a for-all rule as NOT EXISTS, using practice scenarios. It does not sit your assessments or hand you assignment or exam answers, and it cannot promise any grade; the aim is to help you understand the method so you can write the DDL yourself. Always follow the University of Sydney's academic-integrity rules on permitted tools.
Studying with AI? Sia — free AI data science tutor works through COMP9120 step by step.
Exam move
Treat integrity as a checklist you can run on any schema. For each table ask, in order: which domain rules apply (types, NOT NULL, range CHECKs), what the primary key is (and whether it is composite), which foreign keys are needed and what on-delete action each should carry, and whether any rule spans rows or tables (needing an assertion, or a trigger on PostgreSQL). Practise by writing full CREATE TABLE DDL for a small brief and naming the family of every constraint, since the exam marks each correct constraint and the family name. Watch the two classic traps — a CHECK still lets NULL through unless you add NOT NULL, and the on-delete action is declared on the child's foreign key but fires when the parent row is deleted.
