Domain, key, entity and referential integrity; which operations violate what

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. What must every non-NULL foreign key value match? from Lesson 2.2.2
2. What is a candidate key? from Lesson 2.2.1
3. Name the DBMS job that rejects data breaking declared rules. from Lesson 0.1.2

1 · The Hook

The college database will receive thousands of inserts, deletes and updates a day. The DBMS must reject the bad ones automatically: a CGPA of 12, two students with roll 1, a student with no roll at all, an enrolment for a student who does not exist. Each is a different integrity constraint. GATE asks which operation can break which constraint; the answer follows from one picture.

2 · Predict First

Deleting a tuple from STUDENT: can it create a duplicate roll? Can it create a CGPA of 12? Can it break anything at all?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

A delete cannot create a duplicate or an out-of-range value: it adds nothing. It can only break referential integrity: if ENROLL rows still refer to the deleted student, those foreign key values now point to nothing.

The four basic constraints

Domain, key and entity integrity concern one relation. Referential integrity links two relations (or one with itself). Other rules (CGPA ≥ 6 for an honours course) are semantic constraints, enforced with checks, triggers or assertions.

Which operation can violate what

Two direction rules for referential integrity:

• Insert or FK-update in the referencing relation can break it (the new FK points nowhere).
• Delete or PK-update in the referenced relation can break it (old references are left dangling).

Inserting into the referenced relation and deleting from the referencing relation never break referential integrity.

What the DBMS does on a violation

For insert and update, the operation is rejected. For delete (or PK update) of a referenced tuple, the designer chooses an action: reject it, or cascade, or set NULL: the next lesson.

4 · Visual

                     REFERENCED (parent)          REFERENCING (child)
                     STUDENT(roll PK)    <------   ENROLL(roll FK, cid FK)

insert               safe                          may break RI (roll not in STUDENT)
delete               may break RI (roll in use)    safe
update PK / FK       may break RI (old roll used)  may break RI (new roll missing)

RI = referential integrity. Danger comes from "removing a parent" or "adding/changing a child".

5 · Worked Examples

Example 1 Easy

Which constraint does each insert into STUDENT(roll PK, name, cgpa ∈ [0, 10]) violate? (a) (NULL, Riya, 8.0); (b) (1, Ravi, 7.0) when roll 1 exists; (c) (9, Om, 11.5).

Solution

(a) entity integrity; (b) key constraint; (c) domain constraint.

Example 2 Medium

Which operations can violate referential integrity between ENROLL (referencing) and STUDENT (referenced)? Inserting into STUDENT, inserting into ENROLL, deleting from STUDENT, deleting from ENROLL.

Solution

Inserting into ENROLL (new roll may not exist) and deleting from STUDENT (student may still be enrolled). The other two are always safe for this constraint.

Example 3 GATE

"Deleting a tuple can violate the key constraint." True or false?

Solution

False. A delete removes a tuple; it cannot create two tuples with the same key, a NULL key or an out-of-domain value. Delete can violate only referential integrity.

8 · Connect the Dots

Lesson 2.2.2 defined foreign keys; this lesson put them with the other three basic constraints and listed which operations break which. Next: what the DBMS should do when a referenced tuple is deleted or changed: restrict, cascade, set null, and how to trace a chain of cascading deletes.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Entity integrity

Entity integrity requires that:

1. (A) every referenced tuple exists
2. (B) primary key attributes are never NULL
3. (C) foreign keys are never NULL
4. (D) every value is in its domain

Q2 · MCQ · 1 mark(s) — Operations

Which operation can violate only referential integrity among the four basic constraints?

1. (A) Update of a primary key
2. (B) Insert
3. (C) Delete
4. (D) Update of a non-key attribute

Q3 · MSQ · 2 mark(s) — Referential integrity

ENROLL.roll references STUDENT.roll. Which can violate referential integrity?

1. (A) Deleting from STUDENT
2. (B) Inserting into ENROLL
3. (C) Updating STUDENT.roll
4. (D) Deleting from ENROLL

Q4 · MCQ · 1 mark(s) — Domain

Updating a student's cgpa (a non-key, non-FK attribute) to 15 when the domain is 0–10 violates:

1. (A) domain constraint
2. (B) key constraint
3. (C) entity integrity
4. (D) referential integrity

Q5 · NAT · 1 mark(s) — Counting

Of the four basic constraints (domain, key, entity integrity, referential integrity), how many can an insert violate?

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

integrity constraintdomain constraintkey constraintentity integrityreferential integritysemantic constraintviolation by insert / delete / update