Data Integrity #

The database is the single source of truth. Applications can change technologies, architectures can evolve from monolith to microservices, teams can change members — but the data in the database is the only thing that must stay correct through all those changes. The problem is, corrupted data rarely appears as a clear error. It appears silently: financial reports off by one percent, orders that can’t be traced, transactions counted twice with nobody noticing. By the time the problem is discovered, the corrupted data has already spread everywhere — and fixing it takes far more time, effort, and courage than preventing it from the start.

Data integrity is the set of mechanisms that ensure data stays accurate, consistent, and valid — at any time and from wherever the data is accessed or modified. Not just a theoretical concept, data integrity is the operational foundation of a system that can be trusted over the long term.

Why Data Integrity Is Often Ignored #

Before discussing solutions, it’s important to understand why many teams ignore data integrity until it’s too late. The pattern is almost always the same.

Early in a project, teams move fast. Constraints, foreign keys, and database-level validation are considered “later — what matters is getting features out first”. Validation is moved entirely to the application layer because it feels more flexible and faster to change. Database schemas are kept minimal, columns are made nullable “to keep things simple”, and foreign keys are deliberately omitted “so inserts are fast”.

Six months later, the system is accessed by three different services. Each service has its own validation — and each has a slightly different interpretation of the same business rules. Data starts becoming inconsistent. There are orders without users. There are transactions with negative amounts. There are rows with statuses that are logically impossible — but nobody knows since when.

This is the real cost of ignoring data integrity early: not problems that appear now, but problems only discovered months later, already spread across millions of rows, and nearly impossible to roll back.

Four Types of Data Integrity #

Data integrity isn’t a single concept. There are four types, each protecting a different aspect of your data.

Entity Integrity #

Entity integrity ensures that every row in a table can be uniquely and unambiguously identified. This is achieved through the primary key — every table must have one, and primary keys must not be null.

Without entity integrity, you can’t answer simple questions like “how many active users are there?” — because there might be indistinguishable duplicates. Or you can’t do correct joins because there’s no column that can serve as a reliable reference.

-- ANTI-PATTERN: a table without a primary key
CREATE TABLE payments (
    amount       DECIMAL(15,2),
    status       VARCHAR(20),
    created_at   TIMESTAMP
);
-- No way to uniquely identify a single row.
-- Duplicates can't be detected. Joins can't be trusted.

-- CORRECT: always define an explicit primary key
CREATE TABLE payments (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    amount       DECIMAL(15,2)   NOT NULL,
    status       VARCHAR(20)     NOT NULL,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

For new systems, consider using a UUID as the primary key if data will be distributed across multiple databases or services. UUIDs eliminate the risk of collisions when data from multiple sources is merged.

-- An alternative with UUIDs for distributed systems
CREATE TABLE payments (
    id           CHAR(36)        NOT NULL,
    amount       DECIMAL(15,2)   NOT NULL,
    status       VARCHAR(20)     NOT NULL,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

Referential Integrity #

Referential integrity ensures that relationships between tables are always valid. If the orders table has a user_id column, then every user_id value in orders must reference a row that actually exists in users. No order may hang in the air without a clear owner.

This is achieved through the foreign key constraint. And it’s one of the mechanisms most often deliberately omitted for performance reasons — even though the damage it prevents is far more expensive than the overhead it adds.

-- ANTI-PATTERN: table relationships without foreign keys
CREATE TABLE orders (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    BIGINT UNSIGNED NOT NULL, -- a plain number, not a foreign key
    total      DECIMAL(15,2)   NOT NULL,
    PRIMARY KEY (id)
);
-- Nothing prevents user_id from containing a value that doesn't exist in users.
-- If a user is deleted, all their orders become orphans — no owner.

-- CORRECT: define the foreign key explicitly with clear behavior
CREATE TABLE orders (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    BIGINT UNSIGNED NOT NULL,
    total      DECIMAL(15,2)   NOT NULL,
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE RESTRICT   -- refuse to delete a user who still has orders
        ON UPDATE CASCADE    -- automatically update user_id if the user id changes
);

The ON DELETE and ON UPDATE choices must be decided by business rules, not picked arbitrarily:

ON DELETE RESTRICT  → Refuse to delete the parent if children still exist.
                      Use for core data that must not disappear silently.
                      Example: a user who still has active orders.

ON DELETE CASCADE   → Automatically delete all children when the parent is deleted.
                      Use only if children have no meaning without the parent.
                      Example: session logs deleted together with their user.

ON DELETE SET NULL  → Set the foreign key column to NULL when the parent is deleted.
                      Use if the relationship is genuinely optional in business terms.
                      Example: articles whose author is no longer active.

Don’t use ON DELETE CASCADE for financial or transactional data. If a user is deleted and all their payment records are automatically deleted with them, that’s an unrecoverable audit disaster. Use RESTRICT for data with business and legal consequences.

Domain Integrity #

Domain integrity ensures that the value in every column matches its defined domain — in terms of data type, value range, and applicable business rules.

The amount column in a payment table must not be negative. The status column may only contain values defined in the business state machine. The email column must have a valid format. All of this is domain integrity.

-- ANTI-PATTERN: no domain validation at the database level
CREATE TABLE payments (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    amount     DECIMAL(15,2),    -- nullable, can be negative, can be zero
    status     VARCHAR(50),      -- nullable, can contain anything
    PRIMARY KEY (id)
);

-- CORRECT: define the domain explicitly with CHECK constraints
CREATE TABLE payments (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    amount     DECIMAL(15,2)   NOT NULL CHECK (amount > 0),
    status     VARCHAR(20)     NOT NULL CHECK (
                   status IN ('pending', 'processing', 'success', 'failed', 'refunded')
               ),
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

For enum columns whose values are unlikely to change, a CHECK constraint is more flexible than MySQL’s native ENUM type — because adding a new value to a CHECK doesn’t require a table-blocking ALTER TABLE in production.

Transactional Integrity #

Transactional integrity ensures that a series of database operations is treated as one inseparable unit — either all succeed, or all are rolled back. This is the atomicity property of ACID.

Without transactions, you can’t guarantee consistency when one logical operation involves multiple tables at once.

-- ANTI-PATTERN: multi-table operations without a transaction
-- If the INSERT into order_items succeeds but the UPDATE to orders fails,
-- the data is already in an inconsistent state with no way to roll back.
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (101, 5, 2, 150000);

UPDATE orders
SET total = total + 300000, item_count = item_count + 1
WHERE id = 101;

-- CORRECT: wrap it in a transaction
START TRANSACTION;

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (101, 5, 2, 150000);

UPDATE orders
SET total = total + 300000, item_count = item_count + 1
WHERE id = 101;

-- If an error occurs between the two, ROLLBACK undoes all changes.
-- If everything succeeds, COMMIT makes the changes permanent.
COMMIT;

Schema Design That Protects Data Integrity #

Understanding the integrity types is one thing. Applying them in day-to-day schema design is another. Here are concrete principles you can apply directly.

Columns Should Be NOT NULL Unless There’s a Clear Business Reason #

Nullable columns aren’t just a database matter — they create complexity in the application layer. Every nullable column means an additional condition that code must handle: is the value null? What does null mean in this context? Do null and empty string have different meanings?

-- ANTI-PATTERN: all columns nullable because "it's more flexible"
CREATE TABLE users (
    id         BIGINT UNSIGNED,
    name       VARCHAR(100),      -- is a user without a name valid?
    email      VARCHAR(255),      -- is a user without an email valid?
    created_at TIMESTAMP
);

-- CORRECT: NOT NULL is the default, nullable only with a business reason
CREATE TABLE users (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name         VARCHAR(100)    NOT NULL,
    email        VARCHAR(255)    NOT NULL,
    phone        VARCHAR(20)     NULL,     -- optional in business terms
    deleted_at   TIMESTAMP       NULL,     -- null = active, has value = deleted
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY  (id),
    UNIQUE KEY   uk_users_email (email)
);

The rule of thumb is simple: start from NOT NULL for all columns. Change to nullable only if there’s an explicit business decision that the column may legitimately be empty — and document that decision.

A Clearly Defined State Machine #

The status column is one of the most often ignored sources of data corruption. When there are no clear rules about valid transitions, status can change to any value at any time — and the system loses its ability to track whether a state is business-valid.

flowchart TD
    Pending["pending"]
    Processing["processing"]
    Success["success"]
    Failed["failed"]
    Expired["expired"]
    Refunded["refunded"]

    Pending -->|"user pays"| Processing
    Pending -->|"expired"| Expired
    Processing -->|"succeeded"| Success
    Processing -->|"failed"| Failed
    Success -->|"refund requested"| Refunded

Transitions that MUST NOT happen:

✗ failed → success (without a clear reprocessing flow)
✗ success → pending (no business reason)
✗ refunded → failed (a nonsensical state)

Validating these transitions can’t only happen in the application layer — because there are scenarios where the database is accessed directly (data migrations, admin tools, other services). Combine application-level validation with database CHECK constraints for layered protection.

Don’t Use One Column for Multiple Meanings #

This is an anti-pattern that looks “efficient” at first but becomes a nightmare later. Columns whose meaning changes depending on context are a sign the schema wasn’t thought through carefully enough.

-- ANTI-PATTERN: an overloaded "type" column
CREATE TABLE transactions (
    id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ref_id   BIGINT UNSIGNED NOT NULL,  -- could be order_id, refund_id, or topup_id
    type     TINYINT         NOT NULL,  -- 1=payment, 2=refund, 3=topup, 4=???
    amount   DECIMAL(15,2)   NOT NULL,
    PRIMARY KEY (id)
);
-- To query "all refunds this month", you must know that type=2 means refund.
-- No constraint prevents type=9, which has no definition.
-- ref_id can reference any table depending on type — can't be guaranteed with a foreign key.

-- CORRECT: separate by real business meaning
CREATE TABLE payments (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   BIGINT UNSIGNED NOT NULL,
    amount     DECIMAL(15,2)   NOT NULL CHECK (amount > 0),
    status     VARCHAR(20)     NOT NULL CHECK (status IN ('pending','success','failed')),
    PRIMARY KEY (id),
    CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT
);

CREATE TABLE refunds (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    payment_id BIGINT UNSIGNED NOT NULL,
    amount     DECIMAL(15,2)   NOT NULL CHECK (amount > 0),
    reason     TEXT            NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_refunds_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE RESTRICT
);

Unique Constraints for Uniqueness Business Rules #

If a business rule says one user may only have one account with the same email, or one product may only appear once in an order — that’s not validation that can be done in the application layer alone. It must be enforced by the database.

-- ANTI-PATTERN: uniqueness rules only in the application layer
-- If there's a race condition (two requests arriving at once),
-- application-level checks aren't enough — both can pass before either commits.

-- CORRECT: use a UNIQUE constraint or UNIQUE INDEX
-- Unique on a single column:
ALTER TABLE users ADD UNIQUE KEY uk_users_email (email);

-- Unique on a column combination (composite unique):
ALTER TABLE order_items
    ADD UNIQUE KEY uk_order_items_order_product (order_id, product_id);
-- This ensures one product can't appear twice in the same order,
-- even if concurrent requests try to insert at the same time.

Anti-Patterns to Avoid #

All the anti-patterns below share one trait: they don’t produce errors when applied, but they create technical debt with very expensive interest in the future.

-- ✗ Anti-pattern 1: tables without a primary key
-- No way to uniquely identify rows.
-- Duplicates can't be detected, joins can't be trusted.
CREATE TABLE logs (message TEXT, created_at TIMESTAMP);

-- ✓ Solution: always add a primary key
CREATE TABLE logs (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    message    TEXT            NOT NULL,
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- ✗ Anti-pattern 2: foreign keys simulated in the application layer
-- Other services accessing the database directly aren't bound by this rule.
-- Race conditions can produce orphan data.
-- "we'll handle it in code" — the sentence that gives birth to orphan data.
INSERT INTO orders (user_id, total) VALUES (9999, 150000);
-- user_id 9999 doesn't exist in the users table, but the database accepts it.

-- ✓ Solution: define the foreign key at the database level
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;

-- ✗ Anti-pattern 3: validation only in the frontend
-- Frontend validation can be bypassed with Postman, curl, or scripts.
-- Invalid data goes straight into the database if there's no server
-- and database validation.
-- This is the most often ignored gap.

-- ✓ Solution: layered validation — frontend + backend + database constraints
-- Frontend: for responsive UX
-- Backend: for business logic and security
-- Database: as the last safety net

-- ✗ Anti-pattern 4: amount columns without value constraints
CREATE TABLE payments (amount DECIMAL(15,2));
-- amount can be -500000 (negative) or 0. Both are business-invalid,
-- but the database accepts them without complaint.

-- ✓ Solution: add a CHECK constraint
CREATE TABLE payments (
    amount DECIMAL(15,2) NOT NULL CHECK (amount > 0)
);

-- ✗ Anti-pattern 5: soft delete with an is_deleted = 0/1 column
-- Queries must always filter WHERE is_deleted = 0 — easy to forget, easy to leak.
-- No information about when it was deleted or by whom.
ALTER TABLE users ADD COLUMN is_deleted TINYINT DEFAULT 0;

-- ✓ Solution: use a deleted_at timestamp
-- NULL means active, a value means deleted.
-- When it was deleted is stored automatically, queries are more expressive.
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;
-- Active query: WHERE deleted_at IS NULL
-- Deleted query: WHERE deleted_at IS NOT NULL

Data Integrity Review Checklist #

Use this checklist during schema reviews, before migrations, or when onboarding to an existing database.

ENTITY INTEGRITY:
  □ Every table has an explicit PRIMARY KEY
  □ Primary keys are NOT NULL and UNIQUE
  □ No table created without a primary key

REFERENTIAL INTEGRITY:
  □ Every column storing another table's ID has a FOREIGN KEY constraint
  □ ON DELETE and ON UPDATE behaviors consciously decided, not defaults
  □ ON DELETE CASCADE not used for financial or transactional data
  □ No orphan data — every foreign key references a valid row

DOMAIN INTEGRITY:
  □ All mandatory columns are NOT NULL
  □ Nullable columns have documented business reasons
  □ Status columns have CHECK constraints with valid value lists
  □ Amount/quantity/price columns have CHECK constraints for valid ranges
  □ Columns that must be unique have UNIQUE constraints or UNIQUE indexes

TRANSACTIONAL INTEGRITY:
  □ Operations involving more than one table wrapped in transactions
  □ Error handling within transactions correct (ROLLBACK on exception)
  □ Isolation level appropriate for concurrency needs

GENERAL DESIGN:
  □ No overloaded columns (one column for multiple meanings)
  □ State machine defined — which transitions are valid and which aren't
  □ Soft deletes use a deleted_at timestamp, not a boolean flag
  □ Every "why not use a constraint" decision is documented

Summary #

Corrupted data is more dangerous than a clear error — silent data corruption doesn’t crash, but it spreads quietly and can almost never be fully recovered.
Entity integrity is guarded by the PRIMARY KEY — every table must have one, and it must not be nullable.
Referential integrity is guarded by the FOREIGN KEY — don’t leave table relationships unguarded; choose ON DELETE RESTRICT for data with business consequences.
Domain integrity is guarded by NOT NULL, CHECK constraints, and UNIQUE — database validation is the last safety net that can’t be bypassed.
Transactional integrity is guarded by TRANSACTIONS — all multi-table operations must be wrapped in transactions so no half-finished state exists.
NOT NULL is the default, nullable is the exception — the fewer nulls in a schema, the simpler the application logic and the smaller the room for ambiguous data.
Foreign keys aren’t the enemy of performance — the overhead they add is far smaller than the cost of investigating and fixing orphan data in production.
Design state machines explicitly — determine which transitions are valid, and validate them in both the application layer and database constraints.
One column, one meaning — overloaded columns are a sign of an immature schema and a source of hard-to-trace bugs.
Constraints are early warnings — they force errors to appear in the right place at the right time, not months later in the form of unexplainable data.

← Previous: SPoF Next: Replication →