Constraints and Data Integrity

Why it matters

Application validation lives in one code path; data arrives through many: imports, batch jobs, admin screens, other services and two requests racing each other. Any of them can skip the checks the web form performs. Without a constraint the bad row is stored silently and surfaces weeks later as a duplicate customer or an order line pointing to an order that does not exist.

Bad data spreads into reports, invoices and other systems before anyone notices, and repairing it needs a data fix, not only a code fix. For a tester, each constraint is a precise requirement: one statement must be rejected, one must be accepted, and a missing constraint can be shown with a single statement.

Six constraints and what each one refuses

You met primary and foreign keys in the lesson on keys and relationships; here they are two of six constraints. The order_lines table at the end of this section declares all six, and the rest of the lesson tests it.

• NOT NULL: the column must hold a value. An empty string '' is a value, so NOT NULL alone accepts a blank SKU (Oracle is the known exception: it stores '' as NULL).
• UNIQUE: no two rows may share the value, or the combination: UNIQUE (order_id, sku) allows each SKU once per order. NULL is not equal to NULL, so PostgreSQL, MySQL and SQLite accept several NULLs in a UNIQUE column; SQL Server accepts one.
• PRIMARY KEY: UNIQUE plus NOT NULL, at most one per table.
• FOREIGN KEY: the value must exist in the referenced key, or be NULL. Without an ON DELETE clause, deleting a parent that still has children is refused; ON DELETE CASCADE or SET NULL changes that, and the requirement decides which is right.
• CHECK: a condition every row must satisfy. It fails only when the condition is false; a comparison with NULL is unknown, so CHECK (quantity > 0) lets NULL through unless the column is also NOT NULL.
• DEFAULT: the value stored when an insert omits the column. It refuses nothing, and an insert that sends NULL explicitly does not get the default.

-- orders(id) already exists
CREATE TABLE order_lines (
  id        INTEGER      PRIMARY KEY,
  order_id  INTEGER      NOT NULL REFERENCES orders (id),
  sku       VARCHAR(20)  NOT NULL,
  quantity  INTEGER      NOT NULL CHECK (quantity BETWEEN 1 AND 50),
  status    VARCHAR(10)  NOT NULL DEFAULT 'open'
            CHECK (status IN ('open', 'shipped', 'returned')),
  UNIQUE (order_id, sku)
);

Four kinds of data integrity

Textbooks split logical data integrity, the correctness of the stored values, into four kinds; physical integrity, data surviving crashes and disk faults, belongs to backup and recovery testing. Use the four as a checklist: for each kind, find what protects it.

• Entity integrity: every row is identifiable. Protected by PRIMARY KEY, and by UNIQUE on natural keys such as an email address.
• Referential integrity: every reference points to an existing row. Protected by FOREIGN KEY; its violations are orphan rows.
• Domain integrity: every value is valid for its column. Protected by the data type and length, NOT NULL, CHECK and DEFAULT.
• User-defined (business) integrity: rules such as "a doctor cannot have two overlapping appointments". A one-row rule can be a CHECK; a rule across rows or tables usually needs a trigger or application code (see the lesson on stored procedures and triggers).

How to test a constraint

Test at two layers. At the database, prove the rule exists and is enforced. Through the product, prove the refusal becomes a clear message: a declared rule whose error reaches the user as a 500 page is still a defect.

Start from the running schema, not the design document: read the migrations, or query information_schema, the standard views in which the database describes its own tables, columns and constraints, as at the end of this section (PostgreSQL, MySQL and SQL Server provide it). Then, for each rule:

• One rule, two tests, as in boundary value analysis: the value that must be rejected and the closest one that must be accepted — 0 and 1, 51 and 50 for BETWEEN 1 AND 50; 21 and 20 characters for VARCHAR(20).
• Break one column at a time and confirm which rule fired: a row that breaks two rules is refused by whichever constraint the engine checks first. PostgreSQL reports SQLSTATE, the standard five-character error code, 23502 for NOT NULL, 23505 for UNIQUE and PRIMARY KEY, 23503 for FOREIGN KEY and 23514 for CHECK, all in class 23, integrity constraint violation.
• Repeat each violation through the UI and the API: expect a clear message or the 4xx status the API contract defines, such as 409 or 422, never a 500 that shows table or constraint names.
• Test the paths that skip application validation: imports, batch jobs, admin tools and two identical requests at the same moment. A "does this email exist?" check before the insert can let both racing requests through; only UNIQUE stops the second.
• Know your engine: MySQL enforces CHECK only from version 8.0.16, and before 9.0 it parses but ignores a REFERENCES written inside a column definition, as in order_lines, so there the key must be declared separately: FOREIGN KEY (order_id) REFERENCES orders (id). SQLite enforces foreign keys only after PRAGMA foreign_keys = ON and ignores the length in VARCHAR(20).
• Where no constraint protects a rule, audit the stored data with the duplicate and orphan queries of the SQL for Testers guide.

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'order_lines';

SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'order_lines';

Try it: Boundary Value Generator Try it: String Length Counter

Worked example: a constraint test script

Order 7 exists in orders; order 9999 does not. T0 inserts a valid row; each later statement breaks exactly one rule, with the expected PostgreSQL result in its comment.

Run the statements one at a time in a disposable test database. In PostgreSQL a failed statement aborts its transaction, so inside one BEGIN block every statement after T1 would fail for the wrong reason.

-- Disposable test database, autocommit: run one statement at a time.
-- T0 valid row -> accepted; status gets the DEFAULT 'open'
INSERT INTO order_lines (id, order_id, sku, quantity)
VALUES (1, 7, 'A-100', 2);
-- T1 PRIMARY KEY: id 1 again -> rejected, 23505
INSERT INTO order_lines (id, order_id, sku, quantity)
VALUES (1, 7, 'B-200', 2);
-- T2 UNIQUE (order_id, sku): A-100 on order 7 again -> rejected, 23505
INSERT INTO order_lines (id, order_id, sku, quantity)
VALUES (2, 7, 'A-100', 1);
-- T3 FOREIGN KEY: order 9999 does not exist -> rejected, 23503
INSERT INTO order_lines (id, order_id, sku, quantity)
VALUES (3, 9999, 'C-300', 1);
-- T4 NOT NULL: no SKU -> rejected, 23502
INSERT INTO order_lines (id, order_id, sku, quantity)
VALUES (4, 7, NULL, 1);
-- T5 CHECK quantity: 0 -> rejected, 23514 (also 51; 1 and 50 are accepted)
INSERT INTO order_lines (id, order_id, sku, quantity)
VALUES (5, 7, 'D-400', 0);
-- T6 CHECK status: 'lost' is not in the list -> rejected, 23514
INSERT INTO order_lines (id, order_id, sku, quantity, status)
VALUES (6, 7, 'E-500', 1, 'lost');
-- T7 explicit NULL skips the DEFAULT -> rejected by NOT NULL, 23502
INSERT INTO order_lines (id, order_id, sku, quantity, status)
VALUES (7, 7, 'F-600', 1, NULL);

Try it: SQL INSERT Generator Try it: SQL Query Validator Try it: SQL Dataset Generator

Reading the results: the gaps behind the passes

Eight expected results prove that the declared rules work. The findings worth raising usually come from what the rules miss:

• VALUES (8, 7, '', 1) is accepted, because an empty SKU is not NULL. Ask whether the schema needs CHECK (sku <> '') or the API should refuse it.
• T7 hides an API trap: a service that turns a missing JSON field into an explicit NULL fails every request without a status. Send the request once with the field omitted and once with "status": null.
• DELETE FROM orders WHERE id = 7 must be refused while lines exist, unless the requirement says lines go with their order (ON DELETE CASCADE).
• Send the T2 line twice through the API: expect 409 or 422 with a message the user understands, and still one row in the table.
• Whether the status CHECK accepts 'Open' with a capital O depends on the column's collation: PostgreSQL rejects it, while MySQL's default collation accepts and stores it. Test it on the engine you ship on.

Frequently asked questions

References

• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development GroupCheck, not-null, unique, primary-key, foreign-key and exclusion constraints — the rules a database enforces and a tester verifies.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.
• OWASP Cheat Sheet Series: Error HandlingOWASP FoundationError responses that help the caller without leaking stack traces, versions or internal paths.