LEARN. PRACTICE. GROW.Presence and range are different rules
A synthetic movement importer accepts an amount and an external identifier. Its rule requires a known, positive amount. CHECK(amount>0) expresses the comparison, but an unknown result caused by NULL does not violate that CHECK. Also declare NOT NULL when the value is required. A DEFAULT helps fill omitted inputs; it does not replace validation of explicitly supplied values. Before choosing SQL, write a small acceptance table covering NULL, zero, negative, and positive inputs. This table supports discussion with the business and prevents missing information becoming an apparently valid value. The laboratory uses synthetic integers to keep attention on the contract.
Identity belongs to a scope
External identifier R can appear in two tenants without representing duplication. UNIQUE(tenant_id,external_id), with both columns required, protects the relevant combination. Two individual UNIQUE constraints would impose different, more restrictive rules. Also decide what NULL means in an optional key: PostgreSQL 18 UNIQUE allows multiple NULL values by default; NULLS NOT DISTINCT changes that policy. The portable test does not execute that PostgreSQL-specific syntax. For a cross-table reference, a composite foreign key can require task and project to belong to the same tenant. This preserves data integrity; the application remains responsible for checking whether the user may access that tenant.
Relationships and deletion need policy
A nullable foreign key does not require every task to have a project. If the relationship is mandatory, combine the reference with NOT NULL. Then decide what happens when deleting the project. CASCADE may be correct when children are inseparable parts of the parent, but it deletes dependent data. RESTRICT allows refusal while references remain, leaving room for human review. SET NULL represents another rule and requires an absent relationship to be allowed. Test each decision using a project with tasks and another without dependencies. In the SQLite fixture, foreign_keys is enabled and checked before writes; configuration from another connection is not assumed to carry over.
Conflict does not mean equivalent repetition
Two importers can check a key’s absence before either inserts. The preliminary query can improve messages but does not replace write-time uniqueness enforcement. Define the expected outcome when conflict occurs. ON CONFLICT DO NOTHING avoids inserting a row that collides with the chosen key; it does not automatically compare the meaning of every field. If R already represents amount 25 and R arrives with amount 30, returning the old result as though the request were equivalent can hide an error. Keep enough information to compare payload under the contract. This exercise is an idempotency design decision, not a promise of distributed transactions.
Laboratory: constraint scope
The code creates a local table where each tenant has unique external identifiers and required positive amounts. It inserts R in tenant A and R in tenant B: both should exist because the key is composite. Then add isolated attempts using NULL, zero, and a repeated A/R. Predict which rule each attempt violates and confirm the result without reusing production data. A statement failure does not by itself define acceptance of the whole batch; connect this exercise to the transaction lesson and decide whether to roll back every entry. In production, a migration also needs analysis of existing data, locking, available time, and failure recovery.
Summary and import diagnosis
When support receives a rejected write, first identify the violated rule and key scope. Distinguish a missing field, invalid range, nonexistent reference, and reused identity. Do not disable the constraint merely to finish the batch: that changes the contract and can move the problem into later reports. Keep a minimal synthetic sample and test the corrected input or approved rule. Connect this lesson to reconciliation and authorization: consistent data can still be wrong for the request or user. Local tests demonstrate the selected examples but do not replace schema and concurrency validation on the real engine.
CREATE TABLE movements (
tenant_id TEXT NOT NULL,
external_id TEXT NOT NULL,
amount INTEGER NOT NULL CHECK (amount > 0),
UNIQUE (tenant_id, external_id)
);
INSERT INTO movements VALUES ('A','R',25), ('B','R',30);
SELECT tenant_id, external_id, amount
FROM movements
ORDER BY tenant_id, external_idThe same identifier R is valid in different tenants; repeating A/R requires handling a conflict within A’s scope.
Common pitfalls
Do not equate CHECK with NOT NULL, key uniqueness with request equivalence, or cross-tenant integrity with access authorization.
Related topics: Count and group carefully · Indexes and execution plans · Reconciliation, metrics, and query diagnosis
Define presence, value, identity, and relationships separately; decide how the application communicates each rejection.
Reference: Constraints and identity · PostgreSQL 18 reference semantics; DR SQL 2026.4; synthetic plan metrics and portable SQLite 3.51.2 examples