Integrity constraints
The families of rules that keep data correct, seen from the design side: domain, entity, referential, uniqueness, mandatory value and business rules, and what each guarantees. SQL syntax comes later.
- 28 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
- Understand
A few cards from the deck
Integrity constraint: what is it?
Show the answer
A rule that the database system checks on every write, and that refuses any change which would make the data inconsistent.
The database checks it on every insertion, update or deletion, whatever the application writing: that is safer than a check in the form alone.
Rule: "a product's stock cannot be negative". Which family of constraints does it belong to?
- Domain constraint
- Entity constraint
- Referential constraint
- Uniqueness constraint
Show the answer
Domain constraint
The rule limits the values of a single column to a range: that is a domain. It says nothing about row identity (entity), a link to another table (referential) or a duplicate (uniqueness).
A … constraint sets the set of values allowed for a column: a type, a range or a list.
Show the answer
A domain constraint sets the set of values allowed for a column: a type, a range or a list.
The simplest safeguard: it concerns one column alone, without looking at the others.
Domain constraint: what is it?
Show the answer
A rule that limits the values allowed in a column: its type, a range, a list of values or a format.
"A quantity is a positive whole number" or "a status is draft, sent or paid" concern a single column, taken on its own.
Two invoices carry the same number and the accounts no longer add up. Which constraint was missing?
- A domain constraint limiting the allowed values of the invoice number
- A referential constraint tying each invoice to an existing customer
- A business rule capping the maximum amount of each invoice
- An entity or uniqueness constraint on the invoice number
Show the answer
An entity or uniqueness constraint on the invoice number
A duplicate in a column that should tell every row apart betrays the absence of a primary key or uniqueness on it. The other constraints would not have prevented it.