Integrity constraints
Clear, exam-focused notes on integrity constraints in the relational model: definitions, formal laws, SQL examples, common mistakes and practice questions.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
Integrity constraints are rules that keep data correct and meaningful inside a database. Engineers use them when designing schemas and writing DDL/DML so applications cannot store invalid data (for example, duplicate IDs or references to non-existent rows). Good use of constraints reduces bugs, enforces business rules, and helps performance (indexes for keys).
Key ideas
All terms are defined before use.
Database: a collection of related data organized in relations (tables).
Relation schema: a named set of attributes (columns) with domains (types). Example: R(A, B, C) where Dom(A) = integer, Dom(B) = varchar, etc.
Domain: the set of allowed values for an attribute. Domain checks are the simplest constraints: every value in attribute A must belong to Dom(A). Domain constraints check type, range, format.
Tuple: a single row (record) in a relation. A relation instance is a set of tuples.
Integrity constraint (IC): a predicate expressed in the relational model that must hold for every valid database state. If a database state violates an IC, it is invalid.
Types of integrity constraints (common, with formal statements):
Domain constraint (defined above). Formally: for relation R(A1..An), for every instance r of R, for every tuple t ∈ r and every i, t[Ai] ∈ Dom(Ai).
Key-related constraints
- Superkey: a set of attributes K ⊆ {A1..An} of relation R is a superkey if no two distinct tuples in any valid instance of R can agree on all attributes of K. Formal statement:
- For all instances r of R, ∀t1, t2 ∈ r, if t1[K] = t2[K] then t1 = t2.
- Candidate key: a candidate key is a superkey that is minimal: no proper subset of K is also a superkey. Minimality must be stated when asked.
- Primary key: one candidate key chosen by the designer as the main identifier. In SQL, a primary key implies NOT NULL and UNIQUE.
- Key constraint (formal): Given relation R and K a candidate key, every valid instance r of R must satisfy the superkey property above and minimality.
Draw for exam: draw a table named STUDENT with columns (RollNo PK, Name, Dept). Mark RollNo with a key symbol (underline) and annotate: PK ⇒ UNIQUE + NOT NULL.
- Superkey: a set of attributes K ⊆ {A1..An} of relation R is a superkey if no two distinct tuples in any valid instance of R can agree on all attributes of K. Formal statement:
Entity integrity (specific to relational model): Every table (relation) with a primary key must have the primary key attributes NOT NULL. Formal: for relation R with primary key PK, for every tuple t ∈ R, t[PK] contains no NULL. In SQL this is enforced by PRIMARY KEY.
Referential integrity (foreign keys)
- Foreign key (FK): an attribute (or set) F in relation R1 that references a candidate key K of relation R2. It expresses a parent-child relation between rows in R2 (parent) and rows in R1 (child).
- Referential integrity rule (formal): For relations R1 and R2, with R1.F referencing R2.K,
- For every tuple t ∈ R1, either t[F] is NULL (if NULLs allowed) or there exists a tuple s ∈ R2 such that s[K] = t[F].
- Explain options on update/delete of parent rows: ON DELETE/UPDATE CASCADE, SET NULL, SET DEFAULT, NO ACTION/RESTRICT. These control what happens to child rows when parent rows change. For composite foreign keys, SQL MATCH options matter: MATCH SIMPLE (default) means if any FK component is NULL, no match is required; MATCH FULL requires either all components NULL or all non-NULL and matching. For SET NULL, the FK columns must be declared NULLABLE; for SET DEFAULT, a DEFAULT value must be defined on the FK columns.
Assertion / general constraint: an assertion is an arbitrary predicate (often written in first-order logic or SQL) over one or more relations that must be true for all database states. Example: total number of employees in Dept X ≤ 100. In SQL standard this concept exists as CREATE ASSERTION, but many DBMS do not implement it; often the same behavior is emulated with triggers.
CHECK constraint: a predicate on one row (tuple-level) that must hold for each tuple inserted or updated. Example: CHECK (age >= 18). In SQL, a CHECK passes if the condition evaluates to TRUE or UNKNOWN; if NULL makes the condition UNKNOWN, it does not cause a violation.
NOT NULL and UNIQUE
- NOT NULL: attribute value cannot be NULL.
- UNIQUE: attribute (or attribute set) values must be distinct across tuples. Note: SQL semantics about NULL with UNIQUE vary by DBMS: the SQL standard treats NULL as unknown and allows multiple NULLs in a UNIQUE column; some engines treat it differently.
Enforcement modes
- Immediate enforcement: constraint checked at the end of each statement.
- Deferred enforcement: constraint checked at the end of a transaction. (SQL allows DEFERRABLE constraints; support depends on DBMS.)
Assumptions to state in answers: relational model (set semantics), NULL handling follows SQL standard unless DBMS-specific behavior is asked, foreign keys reference candidate keys.
Formulas
Domain constraint: ∀t ∈ r, ∀A ∈ schema(R), t[A] ∈ Dom(A) — t[A] is the value of attribute A in tuple t; Dom(A) is the domain (type/range). Applies always.
Key (superkey) constraint: ∀t1, t2 ∈ r, (t1[K] = t2[K]) ⇒ (t1 = t2) — K is set of attributes used as superkey; equality means equality on every attribute of K.
Minimality for candidate key K: K is candidate key ⇔ K is superkey ∧ ∀K' ⊂ K, K' is not a superkey — K' ⊂ K means proper subset.
Referential integrity: ∀t ∈ r1, (t[F] IS NULL) ∨ (∃s ∈ r2, s[K] = t[F]) — F is foreign key in R1, K is referenced candidate key in R2.
CHECK constraint: ∀t ∈ r, checkPredicate(t) evaluates to TRUE or UNKNOWN (per SQL three-valued logic) — checkPredicate is any boolean expression over attributes of a tuple.
Note on symbols: = means value equality; NULL handling must be specified since NULL comparisons are unknown in SQL.
Worked examples
Example 1 — Key determination (theory question) Question: Given relation R(A, B, C, D) with functional dependencies: A → B, C → D. Show whether {A, C} is a candidate key. Answer (step-by-step):
- Compute closure of {A, C}: {A, C}+.
- Start: {A, C}+ = {A, C}.
- From A → B, add B: {A, C}+ = {A, B, C}.
- From C → D, add D: {A, C}+ = {A, B, C, D} which is all attributes of R.
- Thus {A, C} is a superkey because closure yields all attributes.
- Check minimality: compute closures of proper subsets:
- {A}+ = {A, B} (using A → B) — not all attributes.
- {C}+ = {C, D} — not all attributes.
- No proper subset is a superkey, so {A, C} is a candidate key. Final answer: {A, C} is a candidate key.
Example 2 — SQL code showing referential integrity and cascade SQL (standard-ish): CREATE TABLE Department ( DeptID INT PRIMARY KEY, DeptName VARCHAR(50) NOT NULL );
CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(50), DeptID INT, FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ON DELETE CASCADE );
-- Inserts INSERT INTO Department VALUES (1, 'CSE'); INSERT INTO Department VALUES (2, 'ECE'); INSERT INTO Student VALUES (101, 'Asha', 1); INSERT INTO Student VALUES (102, 'Bala', 1); INSERT INTO Student VALUES (103, 'Chirag', 2);
-- Show tables (sample outputs shown below) SELECT * FROM Department; SELECT * FROM Student;
-- Now delete Department 1 should cascade to Student DELETE FROM Department WHERE DeptID = 1; SELECT * FROM Student;
Sample outputs (exact text depends on client; show tabular rows): After initial inserts, SELECT * FROM Department; returns: DeptID | DeptName 1 | CSE 2 | ECE
SELECT * FROM Student; returns: RollNo | Name | DeptID 101 | Asha | 1 102 | Bala | 1 103 | Chirag | 2
After DELETE FROM Department WHERE DeptID = 1; then SELECT * FROM Student; returns: RollNo | Name | DeptID 103 | Chirag | 2
Explanation: ON DELETE CASCADE removed students 101 and 102 because their DeptID = 1 referenced the deleted department.
Notes: Some DBMS require transactions or slightly different SQL; CREATE ASSERTION is often unsupported, so use triggers for cross-table checks.
Common mistakes
- Forgetting minimality when asked to show candidate keys in FD problems.
- Assuming a FOREIGN KEY must reference a PRIMARY KEY only: theory requires a candidate key; SQL allows referencing any column(s) declared UNIQUE and NOT NULL (DBMS differences exist).
- Not mentioning NULL handling in answers about referential integrity; NULLs change the rule (NULL FK values are allowed unless NOT NULL is specified).
- Mixing up NO ACTION and RESTRICT: NO ACTION defers the check until transaction end if deferrable; RESTRICT checks immediately and prevents the change if child rows exist.
For your exam
Kinds of questions:
- Definitions: define integrity constraint, superkey, candidate key, referential integrity.
- Short notes: explain types of constraints with examples.
- Derivations/proofs: show that a set of attributes is a candidate key using closure computation; prove minimality.
- SQL programs: write CREATE TABLE with PK, FK, CHECK and show sample INSERT/DELETE demonstrating behavior.
- Diagrams: draw a table schema with PK and FK arrows; for referential integrity, draw parent and child relation boxes and an arrow from child.FK to parent.PK labeled WITH ON DELETE action.
What full-marks answers include:
- Precise definitions with keywords (e.g., “minimal”, “for all tuples”).
- Formal statement of the constraint (use predicate logic or closure reasoning for FD problems).
- If SQL asked, exact DDL statements and an explanation of expected behavior.
- Mention assumptions about NULLs and DBMS-specific support if relevant.
Quick check
- Define a candidate key in one sentence.
- State the referential integrity rule for a foreign key F in relation R1 referencing key K in R2.
- In SQL, what does PRIMARY KEY guarantee that UNIQUE does not?
Answers: 1) A candidate key is a minimal set of attributes that uniquely identifies every tuple in a relation. 2) For every tuple t in R1, either t[F] is NULL or there exists a tuple s in R2 with s[K] = t[F]. 3) PRIMARY KEY implies NOT NULL (in addition to UNIQUE); UNIQUE alone may allow NULLs (DBMS-dependent).
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?