Enforcing Data Integrity in Database Design

Data integrity means the database itself refuses to store data that violates the rules of the business, rather than relying entirely on application code to check those rules before every insert or update. Application-level validation is useful for giving users friendly error messages, but it is not a substitute for database-level constraints, because application code can have bugs, can be bypassed by a second application or a direct database script, and can simply be forgotten in one code path out of many.

Designing for data integrity means treating NOT NULL, foreign keys, UNIQUE, and CHECK constraints as first-class design decisions made table by table and column by column — not an afterthought bolted on after the schema is "working."

Why it matters

Bad data is easy to let in and very hard to find and repair afterwards. A price of zero, a product with no name or a row pointing to a category that does not exist will each cause a failure somewhere far from where it entered. Rules written into the schema stop bad data at the door, whichever program is writing.

NOT NULL: Making Required Fields Actually Required

Every column should be explicitly decided as required or optional; NOT NULL is the mechanism that turns "this should always have a value" from a comment or a hope into an enforced guarantee. A column left nullable by default, without a decision, is the single most common source of "why is this field empty in production" bugs — because eventually some code path, some import script, or some manual fix will skip setting it.

Foreign Keys: Preventing Orphaned and Invalid References

A foreign key constraint guarantees that a value like orders.customer_id can never point to a customer that does not exist. Without this constraint enforced at the database level, a bug or a race condition in application code can insert an order referencing a deleted or nonexistent customer, and that broken reference then corrupts every report or join that assumes the relationship is valid.

UNIQUE and CHECK: Guarding Business Rules Directly in the Schema

UNIQUE constraints prevent duplicate values where the business requires exactly one (one email per customer account, one SKU per product). CHECK constraints enforce rules about the value itself, independent of other rows — a quantity must be greater than zero, a discount percentage must be between 0 and 100, a status column must be one of a fixed set of values. These guardrails catch bad data at the moment it is written, which is far cheaper than discovering and cleaning it up after it has spread through reports and downstream systems.

• NOT NULL — a value must always be present.
• FOREIGN KEY — a value must reference an existing row in another table.
• UNIQUE — no two rows may share the same value (or combination of values).
• CHECK — a value must satisfy a specific condition (range, set of allowed values, sign).

Example

CREATE TABLE products (
    id           BIGINT PRIMARY KEY,
    sku          VARCHAR(50) NOT NULL UNIQUE,
    name         VARCHAR(150) NOT NULL,
    unit_price   DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0),
    stock_qty    INT NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
    status       VARCHAR(20) NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'discontinued', 'out_of_stock')),
    category_id  BIGINT NOT NULL REFERENCES product_categories(id)
);

-- These inserts are rejected by the database itself, before any
-- application code even runs:
-- INSERT INTO products (id, sku, name, unit_price, category_id)
--   VALUES (1, NULL, 'Widget', 9.99, 1);              -- NOT NULL violation on sku
-- INSERT INTO products (id, sku, name, unit_price, category_id)
--   VALUES (2, 'SKU-1', 'Widget', -5.00, 1);           -- CHECK violation on unit_price
-- INSERT INTO products (id, sku, name, unit_price, category_id)
--   VALUES (3, 'SKU-1', 'Widget', 9.99, 999);          -- FK violation, category 999 doesn't exist

Each commented insert fails with a distinct, specific database error
(NOT NULL violation, CHECK constraint violation, foreign key violation)
rather than silently storing invalid data. Valid rows must have a
non-null unique SKU, a non-negative price, non-negative stock, a status
from the fixed allowed set, and a category that actually exists.

Exercise

Create a products table with four guards: name is required; sku is unique; price must be greater than zero; and category_id must refer to an existing category. Of the four inserts below, only the first is valid. Count the rows that were accepted.

Expected output: total then 1

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO categories (id, name) VALUES (1, 'Stationery');

-- TODO: products (id, name, sku, price, category_id) with the four guards

INSERT INTO products (id, name, sku, price, category_id) VALUES (1, 'Pen', 'PEN-1', 12, 1);
INSERT INTO products (id, name, sku, price, category_id) VALUES (2, 'Pencil', 'PEN-1', 5, 1);
INSERT INTO products (id, name, sku, price, category_id) VALUES (3, 'Ruler', 'RUL-1', 0, 1);
INSERT INTO products (id, name, sku, price, category_id) VALUES (4, 'Bag', 'BAG-1', 450, 9);

SELECT COUNT(*) AS total FROM products;

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO categories (id, name) VALUES (1, 'Stationery');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  sku TEXT NOT NULL UNIQUE,
  price INTEGER NOT NULL CHECK (price > 0),
  category_id INTEGER NOT NULL REFERENCES categories(id)
);

INSERT INTO products (id, name, sku, price, category_id) VALUES (1, 'Pen', 'PEN-1', 12, 1);
INSERT INTO products (id, name, sku, price, category_id) VALUES (2, 'Pencil', 'PEN-1', 5, 1);
INSERT INTO products (id, name, sku, price, category_id) VALUES (3, 'Ruler', 'RUL-1', 0, 1);
INSERT INTO products (id, name, sku, price, category_id) VALUES (4, 'Bag', 'BAG-1', 450, 9);

SELECT COUNT(*) AS total FROM products;

Quick quiz

Interview questions