26 min read
Data Integrity and Invalid Data
George types a phone as a sum, a future return date, a blank name, and Yrk. Allowed-looking values can still be wrong.
George Clarke is covering the desk at Riverside Public Library in York while Amelia Hart is on a break. He wants to be helpful. He types.
For a phone he treats the digits as a number and writes 7700900456+1 — a sum. The leading zero is already gone.
For a return date he types 14 May 2027. It looks like a date. It is a year in the future.
He saves a member row with an empty FullName, because the person had stepped away from the form.
He types the city Yrk. It is almost York. It is not York.
Oliver Bennett looks at the screen. "None of those exploded," he says. "They still should not have been allowed."
A value can look like the right kind of thing and still be wrong.
7700900456+1 is made of digits and a plus sign. It is not 07700 900 456. A phone is not a number you add to. It is a string of digits you copy.
14 May 2027 is a date. The book was borrowed on 14 May 2026. A return a year later is not a library rule; it is a mistype that survived because it was shaped like a date.
A blank FullName is an empty box. The library now has a member who cannot be addressed, found, or printed on a card.
Yrk is letters. It sits in a city column. It is not on Oliver's list of cities: York, Bristol, Manchester, Leeds, London.
Invalid data is not always "banana" in a date field. Often it is something that nearly fits.
| George's typing this afternoon | ||||
|---|---|---|---|---|
Rules and constraints
The database can enforce constraints — rules that must stay true before a row is saved.
A value must belong to the allowed set for that field. Phone must be copied digits, stored as text — not a sum. City must be a real city from the list. ReturnDate must be a date that makes sense for a two-week loan, not next year. FullName must not be blank if every member is required to have a name. The textbook name for that rule is domain integrity. Domain here just means "the allowed set for this field."
Every row must be identifiable. MemberID must be present and unique. You cannot have two members with MemberID 1. You cannot have a member with no MemberID. A blank name is a domain problem; a missing or duplicated MemberID is an identity problem. The textbook name for the identity rule is entity integrity.
Data integrity, in beginner language, is the habit of keeping stored values complete enough and consistent enough to do the job. The computer can catch many of George's mistakes. It cannot catch every lie: York is on the list, and someone can still pick it when the form said Leeds.
Domain integrity vs entity integrity
These two rules answer different questions.
Domain integrity asks: is this value legal for this field? Is the phone copied text, not a sum? Is the city on the list? Is the name filled in if required?
Entity integrity asks: can this row be identified? Is the primary key present? Is it unique?
A blank FullName breaks domain integrity — the field is not allowed to be empty. Two rows sharing MemberID 1 would break entity integrity — the primary key would no longer identify one row.
Both are part of data integrity. They are not the same check.
Two different questions
| Domain integrity | Entity integrity |
|---|---|
| Is this phone copied text, not a sum? | Is MemberID present and unique? |
| Is City York, Bristol, Leeds, or London? | Can two rows share the same MemberID? |
| Is FullName blank when it must not be? | Is there a row with no primary key? |
Practice
Swipe or use the arrows to move between questions.
1 / 3
More lessons in Database Fundamentals · Next: Orphan Records and Referential Integrity · Previous: One-to-One