Data integrity means stored values remain valid under the rules your application relies on. Referential integrity is the part that keeps relationships connected to real parent rows. A form can validate input, but other applications, scripts, and concurrent requests can write to the same database. Constraints provide a shared enforcement boundary. This reference explains types, nullability, checks, uniqueness, and foreign keys, then tests a small order schema. You will also learn the limits of row-local checks and the importance of actual engine enforcement. A constraint written in a file helps only when the deployed database enforces the intended rule.
Use layers rather than one universal check
Column types define a broad domain, such as integer, decimal, or date. NOT NULL rejects absence, CHECK expresses a supported row rule, UNIQUE prevents prohibited duplicates, and primary keys identify rows. Foreign keys connect rows across tables. Application validation supplies helpful messages before a write, while database constraints protect all writers and concurrent cases. Authorization is another layer: valid data can still be written by the wrong account unless permission is checked. A text column does not guarantee a meaningful email, and a valid email does not prove ownership. Assign each business rule to an appropriate enforcement mechanism and document what remains outside the database's declarative constraints.
Handle NULL under the actual rule
NULL represents missing or unknown information, and SQL comparisons involving it usually produce unknown. CHECK expressions in common engines can accept unknown rather than only true, so CHECK(price >= 0) alone may still allow a null price. Add NOT NULL when the value is required. UNIQUE treatment of NULL differs by dialect and options; do not assume every engine permits or forbids the same null duplicates. A nullable foreign key commonly allows an absent relationship, while a non-null value must match the referenced key. Empty strings are not automatically NULL in most engines, though dialect behavior differs. State whether missing, empty, and zero are meaningful separate values for the feature.
Choose candidate keys and uniqueness deliberately
A primary key is a selected candidate key and must uniquely identify each row. A unique constraint can protect a business code, email, or combination such as tenant_id and external_reference. Composite uniqueness protects the pair, not each component individually. Collation influences whether text values compare as equal, including case and accents, so an email uniqueness policy needs a documented comparison rule. A surrogate ID does not remove the need for a business unique key when duplicates are prohibited. Preliminary SELECT checks cannot guarantee uniqueness under concurrency. Keep the constraint and translate expected violations into application results. Review duplicate cleanup before adding a new constraint to an existing table.
Enforce references and lifecycle actions
A foreign key points to a permitted primary or unique parent key under the dialect's rules. It prevents an order from referencing a nonexistent customer when enforced. ON DELETE actions decide whether parent deletion is rejected, cascades, or leaves a nullable reference. Choose them from retention policy, not convenience. An archived customer row still satisfies the reference even if the application considers it inactive, so active-state rules need additional logic. Child-key indexes can help queries and referential operations, but engines differ in automatic creation. In SQLite, enable foreign_keys for each connection according to the documented runtime behavior. In MySQL, use an engine and version supporting the intended constraints.
Know what a row CHECK cannot guarantee
A CHECK can compare columns in the same row, such as end_date >= start_date. It generally cannot safely enforce arbitrary counts or conditions in other rows. 'A session has at most twenty active bookings' is an aggregate concurrency rule, not a simple per-booking check. A reviewed transaction, locking pattern, or engine-specific mechanism is needed. Triggers can express some additional rules, but they introduce ordering, concurrency, and maintenance concerns. Do not assume reading the current count and then inserting is race-free. Likewise, a foreign key does not require every parent to have a child. Enforcing a confirmed order's minimum item count belongs to a checked state transition or another deliberate design.
Verify and maintain deployed integrity
Test forbidden values in a disposable database and confirm the engine rejects them. Inspect actual constraint metadata after migrations rather than only source definitions. Legacy versions may ignore or differently enforce syntax that modern versions support. Loading data with constraints disabled requires a controlled validation and re-enabling procedure; do not declare integrity restored merely because the import finished. For a new constraint on existing data, audit and repair violations before activation under the engine's migration options. Name constraints where helpful so errors can be classified without exposing raw messages publicly. Backup and restore procedures should verify constraints, relationships, and representative data, not only successful file transfer.
Practice task and worked solution
Add integrity rules to a product import table. Require a nonempty application-validated code, make the business code unique under a chosen collation, require a nonnegative decimal price, and reference an existing category. The solution distinguishes NOT NULL from the row CHECK and tests a null price separately from a negative price. Audit duplicates and orphan categories before adding constraints to existing data. Then describe how a capacity rule spanning many reservations would be enforced under your chosen engine's transaction model. Verify constraints through metadata and actual rejected writes, including the SQLite connection setting if using that dialect.
Continue learning
Review constraints, relationships, and transactions.
The PostgreSQL constraint reference explains CHECK null behavior; the SQLite guide documents connection enforcement.
Data Integrity and Referential Integrity Examples
Each example shows the code, the output it produces and what each part does.
A required customer and positive quantity
Use explicit IDs in a disposable schema. The generic constraints require a supporting engine with enforcement enabled.
The successful row references customer 1 and has a positive quantity. The commented negative tests demonstrate three distinct boundaries: foreign key, check, and not-null. Exact error codes and messages depend on the engine.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
email VARCHAR(150) NOT NULL UNIQUE
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
quantity INTEGER NOT NULL CHECK (quantity > 0)
);
INSERT INTO customers VALUES (1, 'ada@example.com');
INSERT INTO orders VALUES (10, 1, 2);
SELECT order_id, customer_id, quantity FROM orders;
-- Separate negative tests, each expected to fail:
-- INSERT INTO orders VALUES (11, 999, 1);
-- INSERT INTO orders VALUES (12, 1, 0);
-- INSERT INTO orders VALUES (13, 1, NULL);order_id | customer_id | quantity
10 | 1 | 2
Negative tests: rejected by the stated enforced constraints.Data Integrity and Referential Integrity FAQ
Does CHECK(price >= 0) imply NOT NULL?
Not generally. A CHECK condition that is unknown because the value is NULL passes, so a missing price is still accepted. Add a required-value constraint, NOT NULL, when the price must be present.
Does a foreign key require children?
No. It prevents invalid child references, values that point to no parent, but it doesn't require any parent to have children. A customer with no orders is perfectly valid under a foreign key on orders.customer_id.
Can pre-checking uniqueness remove races?
No. Two requests can both check that a value is free and then both insert it. The database unique constraint remains necessary as the final guard; handle its duplicate-key error and turn it into a friendly message.
Does CASCADE fit all relationships?
No. Choose actions from lifecycle and retention rules: cascading suits dependent data such as order lines when an order is removed, but historical records such as payments usually need RESTRICT so they can't disappear with a parent.
Where should data rules be enforced: in the database or the application?
Both, for different reasons. The application validates early and gives friendly messages, while database constraints guarantee the rules for every writer, including scripts, imports and future applications. Rules that protect integrity, such as keys, uniqueness and required values, belong in the database.
Data Integrity and Referential Integrity Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Two requests both pass an email-exists pre-check. What enforces uniqueness?
Data Integrity and Referential Integrity Quiz
2 questions to check what you learned.
Which combination requires a nonnegative price to be present?
- CHECK(price >= 0) alone while price remains nullable
- NOT NULL plus CHECK(price >= 0)
- NOT NULL alone with no value-range rule
- A DEFAULT 0 while null and negative values remain allowed
What does a child foreign key prevent?
- Every parent having no children
- Every valid reference pointing to an inactive parent under application rules
- A non-null reference to a nonexistent permitted parent
- Every unauthorized user reading an otherwise valid row