Data Engineering Interview: What Problem Does PostgreSQL 18 NOT ENFORCED Solve?

Question and scenario

A legacy orders table contains a small number of customer_id values with no parent customer. The team wants to declare the relationship in PostgreSQL 18 so the target schema is explicit, without blocking a one-shot repair of historical data. Decide whether NOT ENFORCED fits, explain how it differs from a normal foreign key, show how to find violations, and define a staged switch plus proof that the data is ready.

What the interviewer is testing

• Distinguishing a declared business intention from database rejection of invalid writes.
• Explaining that NOT ENFORCED does not check writes and therefore is not a complete runtime integrity guarantee.
• Designing historical scans, incremental monitoring, repair batches, and a final switch gate.
• Identifying dump, restore, cross-service writers, rollback, and client compatibility risks.
• Explaining why an unverified constraint must not be treated as a query-optimizer assumption.

Clarifying questions before answering

• Is this a foreign key or a CHECK? What are the violation volume, growth rate, and repair owner?
• Are writes limited to PostgreSQL, or do ETL jobs, batch scripts, and direct tools bypass the application?
• Can legacy clients handle the new constraint name, migration lock, and a failed switch?
• Must the final switch reach zero violations, or can it complete tenant by tenant?
• What evidence proves that every partition and watermark was scanned?

30-second answer framework

“NOT ENFORCED is useful for declaring a CHECK or foreign-key intent while legacy dirty data remains; PostgreSQL will not check new writes for it, so it is not an integrity guarantee. I would establish a full violation baseline, run incremental checks and alerts for new writes, repair or quarantine historical rows, and switch to enforced only after zero violations and a restore drill. During the transition, clients and optimizers must not treat the declaration as verified truth.”

Step-by-step deep answer

State the semantics and risk

PostgreSQL 18 allows CHECK and foreign-key constraints to be specified as NOT ENFORCED. The database stores the declaration but does not check writes as it would for an enforced constraint. The metadata supports documentation, governance, and migration coordination; it does not replace application, ETL, or independent data-quality checks.

Build full and incremental evidence

For a foreign key, run an anti-join to find child rows without a parent; for a CHECK, run the predicate's negation. Record scan time, snapshot or watermark, partition range, and a result digest. Then check inserts and updates in CDC, write jobs, or quality tasks so a one-time historical scan cannot hide a new bypass path.

SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.customer_id IS NULL;

Design repair and switch gates

Classify orphan orders as automatically repairable, requiring business confirmation, or requiring quarantine. Give the repair an idempotency key, batch limit, and stop condition. Before switching, require a zero full scan, a clean incremental window, a successful backup-and-restore drill, and coverage of every writer. Run the enforced migration in a low-traffic window and watch lock waits and error rates.

Handle recovery and environment drift

If the migration fails, keep the NOT ENFORCED declaration and repaired records, roll back the application release and quality job, and do not claim the constraint is enforced. Test dumps, restores, read replicas, disaster-recovery clusters, and old clients. Confirm that target environments understand conenforced metadata rather than relying only on ORM display.

High-quality sample answer

“I treat NOT ENFORCED as a migration contract, not a database guardrail. I would run a complete anti-join over orders and customers, record partitions and watermarks, then let CDC check new writes. Orphans are classified into automatic repair, business confirmation, and quarantine, with idempotent replay. Only after full and incremental checks are clean, restore testing passes, and every writer is covered would I switch the foreign key to enforced in a low-traffic window and monitor lock waits. At no point would a client or query optimizer assume the declaration proves integrity.”

Common errors

• Error: Seeing a foreign key in the schema and assuming data is consistent → Why it fails: NOT ENFORCED does not check writes → Correction: Produce full and incremental quality evidence.
• Error: Scanning the historical table once → Why it fails: A new bypass writer can create violations → Correction: Cover CDC, ETL, scripts, and application writes.
• Error: Switching directly to enforced → Why it fails: Dirty data or locks can abort migration → Correction: Repair, rehearse, gate, then switch in a low-traffic window.
• Error: Treating an unenforced declaration as an optimization hint → Why it fails: A declaration is not proof of integrity → Correction: Rely only on verified statistics and supported database behavior.

Follow-up questions and responses

Can a CHECK constraint reference another table for a cross-table rule?

Do not rely on it. A row-level CHECK cannot guarantee a global condition after other rows change, and dump/restore order can expose the flaw. Prefer a foreign key or an independent quality task for cross-table relationships.

What if the violation count never reaches zero?

Keep NOT ENFORCED, place violations in quarantine or an explicit business-exception list, and set a growth cap, owner, and expiry date. If the constraint is documentation rather than a near-term executable contract, reassess whether it belongs in the schema.

Can application validation replace an enforced foreign key?

Only as a transition or supplement. Multiple writers, concurrency, and direct scripts bypass application checks; the final integrity boundary should be a database constraint or a verifiable data pipeline.

How do you prove a disaster-recovery replica has not drifted?

Run the same quality query on primary and restored replicas, compare watermarks, violation counts, and result digests, and include restore drills in the switch gate.

References

• PostgreSQL Documentation 18: Constraints
• PostgreSQL Documentation 18: CREATE TABLE
• PostgreSQL Documentation 18: Release Notes
• Greg Low: SQL Interview: 64 Disabling and reenabling constraints
• MockIF: SQL Interview Questions 2026

Answering tip

State that NOT ENFORCED declares a constraint without checking writes, then give the baseline, incremental monitoring, repair classes, switch gate, and restore evidence.

One-sentence takeaway

NOT ENFORCED is a visible migration contract; data engineers still need independent evidence before adopting enforced runtime protection.