Entity, Referential and Domain Integrity

Concept

Integrity constraints are the rules an instance must satisfy to be valid. The relational model defines three, and a DBMS enforces them on every access path — application, script or manual edit alike.

1. Entity integrity

No attribute of a primary key may be null.

The reason is not arbitrary. A primary key exists to identify tuples. Null means unknown or not applicable, and an unknown value cannot identify anything. Worse, null is not equal to null, so two tuples with null keys could not even be distinguished from each other.

  STUDENT ( roll_no PK, name, dept_code )

     21     Meera   CS     valid
   (null)   Ravi    CS     REJECTED - entity integrity

  For a COMPOSITE primary key, NO part may be null:

  ENROLMENT ( roll_no, course_code, marks )
     PK ( roll_no, course_code )

     21   CS201   87     valid
     21   (null)  74     REJECTED - part of the key is null

2. Referential integrity

Every non null foreign key value must appear as a primary key value in the referenced relation.

A foreign key may be null, meaning the relationship simply does not exist for that tuple. What is forbidden is a value that looks like a reference but points at nothing — a dangling reference.

  DEPARTMENT ( dept_code PK, dept_name )
       CS   Computing
       EC   Electronics

  STUDENT ( roll_no PK, name, dept_code -> DEPARTMENT )

     21   Meera    CS      valid   - CS exists
     22   Ravi   (null)    valid   - not attached to any dept
     23   Anitha   ZZ      REJECTED - no such department

  The rule is checked on INSERT and UPDATE of the child, and
  on DELETE and UPDATE of the parent.

3. Domain integrity

Every value of an attribute must belong to that attribute domain.

Other constraint categories

The last row matters. A rule such as a student may take at most eight courses or the head of a department must work in that department is not expressible with a key or a foreign key. Recognising which rules fall outside the model, and saying where they will be enforced instead, is exactly what a design review looks for.

Worked example

-- Illustration only, to show where each rule is declared.
--
-- departments ( dept_code CHAR(2) PRIMARY KEY      -- entity integrity
--             , dept_name VARCHAR(60) NOT NULL )   -- domain
--
-- students ( roll_no   INT PRIMARY KEY             -- entity integrity
--          , name      VARCHAR(60) NOT NULL        -- domain
--          , email     VARCHAR(120) UNIQUE         -- key constraint
--          , marks     INT CHECK (marks BETWEEN 0 AND 100)  -- domain
--          , dept_code CHAR(2) NOT NULL            -- total participation
--          , FOREIGN KEY (dept_code)
--              REFERENCES departments (dept_code) )-- referential integrity

-- Every one of these rejections happens in the DATABASE,
-- so a new application, a script or a manual edit cannot
-- bypass them.

Why constraints belong in the database

• Every access path is covered. Application code protects only the paths that go through it.
• They cannot drift. Two applications cannot disagree about a rule declared once.
• They are documentation that is guaranteed true. Reading the schema tells you the real rules.
• The optimiser uses them. A declared uniqueness or not null constraint lets the planner choose better plans.
• They fail loudly. A rejected insert is visible immediately; corrupt data discovered months later is not.

Common mistakes

• Saying a foreign key can never be null. It can, unless declared not null. That is how optional relationships are expressed.
• Allowing part of a composite primary key to be null. Entity integrity forbids every part.
• Enforcing rules only in the application. The next application will not know them.
• Confusing domain integrity with referential integrity. One restricts values, the other restricts references.
• Assuming every business rule can be a constraint. Count limits and cross row rules usually cannot.

Exam and interview questions

1. State the three integrity rules of the relational model.
2. Why can a primary key not be null, but a foreign key can?
3. Give three examples of domain constraints.
4. Give a business rule that no key or foreign key can express, and say where you would enforce it.
5. List four reasons to declare constraints in the database rather than the application.

Practice

1. For the e-commerce schema of Phase 4b, list every entity, referential and domain constraint.
2. Given four candidate rows, decide which integrity rule each one violates.
3. Name three rules from the college project that must be enforced outside the model, and say how.

Conclusion

Entity integrity protects identity, referential integrity protects relationships, and domain integrity protects values. Declare all three in the database, and be explicit about the business rules that fall outside the model and therefore need enforcing somewhere else.