Why Unique Constraints Miss Duplicate Order Imports: Business Keys, NULL and Conflict Acceptance

An auto-generated primary key does not prevent duplicate business facts. A ten-record example separates delivery retries, unknown identities, conflicting revisions and valid corrections, with PostgreSQL 18 designs and failure tests.

A unique constraint protects the key it is given; it does not discover business identity. Reliable imports need an agreed fact grain, key scope, null policy and response to different content under the same identity. This discussion assumes PostgreSQL 18, one target primary database and authorized order-line imports. SQL is illustrative design, with reproducible numerical expectations; no concurrent database experiment is claimed. Cross-database atomicity and automatic entity resolution without reliable identity are outside scope.

Define business identity before adding a generated key

Assume the imported object is the current order line, rather than an order header or payment event. Its identity comprises tenant_id, source_system, order_no and line_no. Each identity can have multiple revisions. source_revision represents a reliable increasing revision within the relevant source and line; amount is decimal, tax-exclusive money in one currency. An order-header feed needs a different grain and must not inherit this line key without examination.

A generated row_id gives every insert a different database identity without preventing repeated source facts. Filename, batch number and received row position identify ingestion attempts, not necessarily business objects: reimporting a file creates a new batch, not a new sale. Keep receipt identity, business identity and revision identity separately to distinguish technical retries, corrections and genuine additions.

PostgreSQL 18 defines composite uniqueness over a column combination and makes primary keys both unique and non-null.[1] The business still chooses that combination. Omitting tenant merges unrelated enterprises; omitting source can conflate identical numbers from ERP and after-sales systems; omitting line merges separate products within an order.

Whether similar customer names represent one entity is an evidence-based matching problem, not something an index decides. This example assumes source owners approved the four-column identity and revision meaning. Missing line identifiers go into an unresolved-identity area. A stable alternative identifier can support a separately approved mapping; row position, amount or product name should not be improvised into an alleged recovered identity.

Trace ten received records into three current facts

The following hypothetical records arrive in order. Unless noted, they belong to tenant T1, source ERP and order O7. R1 is line 1, revision 1, amount 100; R2 repeats R1 exactly; R3 is line 2, revision 1, amount 60. R4 has NULL line number, revision 1 and amount 30; R5 repeats that incomplete content. R6 belongs to tenant T2, with ERP, O7, line 1, revision 1 and amount 80. NULL denotes an unknown value, not text.

The first stage confirms three current facts: T1 line 1 at 100, T1 line 2 at 60 and T2 line 1 at 80, totaling 240. R2 is an evidenced replay. R4 and R5 each retain receipt evidence in quarantine: matching other fields does not prove that two unidentified lines are one business object. Received amounts total 400, including 100 of known replay and 60 of unresolved input, so 400 is not new sales.

R7 then claims that T1 line 1, revision 1 is worth 120, contradicting the accepted 100. Quarantine it and block automatic advancement of that key. The source owner establishes that R7 was an erroneous export, closes the conflict and supplies a valid revision 2: R8, amount 90. Evidence and approval precede activation of the correction; arrival order alone does not settle it.

Finally, R9 replays T1 line 1 revision 1 at 100, and R10 repeats T1 line 2 revision 1 at 60. The final current state must be T1 line 1 revision 2 at 90, T1 line 2 revision 1 at 60, and T2 line 1 revision 1 at 80: three facts totaling 230. The ten received amounts total 770, but that sum combines attempts, exceptions and versions and has no sales meaning under this contract.

Replace the old contribution of 100 with 90 to move from 240 to 230; adding 90 would incorrectly yield 330. Replaying all ten inputs must leave the same three facts and 230, while preserving conflict and missing-identity outcomes. Compare complete keys, revisions, amounts and exception sets. A total alone can conceal one incorrect merge offset by one extra record.

Choose nullable-key behavior from what NULL means

A legacy UNIQUE (tenant_id, source_system, order_no, line_no) with nullable line_no can admit both R4 and R5. PostgreSQL's default uniqueness comparison does not equate nulls; NULLS NOT DISTINCT changes that rule.[1] The database is implementing a null policy different from the business expectation, rather than failing to enforce its definition.

For these order lines, make all four columns NOT NULL and use their combination as the primary key. Missing line identity makes the object unknowable. NULLS NOT DISTINCT would reject a second unknown line but would not prove the first one's identity. Collapsing many unknown objects into one unknown object also damages facts; an improved deduplication percentage is not evidence of correctness.

A different, explicit domain can justify NULLS NOT DISTINCT: a configuration table might define an absent region as one default configuration per tenant and metric. That absence is a controlled category, not an unidentified customer or line. Define the category before the constraint. Avoid inventing COALESCE(line_no,0) as a sentinel when zero could be a legitimate identifier.

The method for SQL null filtering and aggregation addresses query-time three-valued logic; this article addresses identity during writes. Both require checks. Correct handling of NULL in a query does not establish unique imports, and uniqueness does not establish sufficient input identity. Likewise preserve original strings and check collisions before normalizing case, whitespace or encoding; normalization must not silently redefine source identity.

Classify staging records before enforcing final facts

A staging area can retain import_id, received row position, original business key, source revision, normalized fields, content digest and disposition. It accepts duplicate or incomplete receipts as evidence; the final fact table enforces the approved non-null key. Exceptions need reasons, original record locations, owners and retest conditions, with access controls appropriate to the business data.

Check identity completeness, amount and currency meaning, equal-revision content consistency, ordering of different revisions and trust in the source version. Identical replays need not create facts. Different content at the same revision is a conflict; incomparable versions suspend automatic processing. Receipt time must not substitute for a source revision, and numbers from unrelated source scopes cannot be compared.

Detect same-batch conflicts before choosing winners. If a fresh batch presents both 100 and 120 for the same key and revision, quarantine the group until adjudicated. With valid revisions 1 and 2, select a current candidate according to the approved rule while retaining revision evidence. Sorting and taking one row is meaningful only after conflict exclusion and validation of the ordering field.

PostgreSQL rejects ON CONFLICT DO UPDATE inputs that would affect one target row more than once in a statement.[2] Candidate grouping therefore supports statement correctness as well as business interpretation. It is not the final defense: another batch may process the same key concurrently, so final uniqueness and the transaction protocol remain necessary.

Compare immutable facts with revisioned current state

Path A accepts immutable facts. Same-key, same-content input is a replay; changed content enters a controlled correction process rather than overwriting through an ordinary retry. This can fit sources without reliable revisions, modest volumes and changes requiring approval. A replacement batch can construct a new result for verification and controlled cutover. Count correction delay and human effort instead of hiding them behind a zero-duplicate metric.

Path B keeps a revision ledger plus current state. The ledger identity is the four-column business key and source_revision, with normalized content or verifiable fields, source evidence and outcome. The current table uses the four-column key and accepts only approved newer versions. This suits frequently corrected, replayable sources with stable revision order, but adds storage, conflict processing and maintenance. Ledger, current state and processing outcome need one consistent target transaction.

An illustrative current table is: CREATE TABLE order_current (tenant_id text NOT NULL, source_system text NOT NULL, order_no text NOT NULL, line_no integer NOT NULL, source_revision bigint NOT NULL, amount numeric(18,2) NOT NULL, PRIMARY KEY (tenant_id, source_system, order_no, line_no), CHECK (source_revision > 0));. Production still needs approved value domains, permissions, source generations and audit fields.

A digest is not all the evidence. Define which normalized business fields participate, whether 100 and 100.00 are equivalent, and which receipt metadata or comments do not affect business content. A digest accelerates comparison; adjudication must still reach controlled original fields without putting sensitive payloads in routine error logs. Version normalization rules so schema changes do not unexpectedly turn the same source record into different content.

Debezium 3.7 describes at-least-once delivery and conditional exactly-once support through Kafka Connect.[6] Check the actual deployment's boundary. No messaging guarantee in this discussion establishes atomicity across order tables, aggregates and external notifications. Even a once-delivered event can overlap a separate import of the same business record.

Give replay, conflict and correction different transactional outcomes

One design for Path B begins a transaction and attempts to register the revision identity. If it exists, lock and read it, then compare normalized content. Matching content returns the stored outcome without changing current state again. Different content records a conflict and blocks automatic advancement. For a new revision, verify that no unresolved conflict exists and that source ordering is valid, then attempt advancement and persist the accepted or obsolete outcome in the same transaction. The blocked state must itself remain queryable.

The core advancement can be expressed as: INSERT INTO order_current AS c (tenant_id, source_system, order_no, line_no, source_revision, amount) VALUES ($1,$2,$3,$4,$5,$6) ON CONFLICT (tenant_id, source_system, order_no, line_no) DO UPDATE SET source_revision=EXCLUDED.source_revision, amount=EXCLUDED.amount WHERE EXCLUDED.source_revision > c.source_revision RETURNING source_revision, amount;. Bind parameters through a controlled importer rather than concatenate model-generated input.

This statement advances revision order; it does not check equal-version content conflicts. Without the ledger, equal-revision amounts 100 and 120 can both look like no update, concealing the discrepancy. All write paths must also observe the same per-business-key conflict gate. A pre-created, locked key-state row is one implementation; a process-local boolean is insufficient.

R8 should commit revision-2 evidence at 90 and replace the current 100. R9 finds historical revision 1 at 100 and returns a historical-replay outcome without downgrading current state. If maintaining preaggregates, adjust them consistently with the current-row change or recompute from committed facts. The example chooses recomputation of three current rows to obtain 230, reducing incremental compensation complexity.

A zero-row return needs an explanation: obsolete version, identical replay, blocked conflict or another error are different outcomes. Separate receipts, accepted revisions, current facts and unresolved exceptions. Reporting ten successful imports must not imply ten valid sales lines. This is a protocol requiring integration validation, not a complete production driver implementation.

Bind adjudication to the business key, revision and exact disputed content. An exact replay of R7 after its erroneous export has been resolved should return the recorded rejection, not reopen the same conflict indefinitely. A different, unadjudicated payload still blocks advancement. Preserve recoverable approval and rejection records rather than merely emptying an exception table; otherwise replay can repeatedly interrupt valid service even while leaving the amount unchanged.

Concurrency and soft deletion change the scope of uniqueness

Two sessions can both observe absence before either inserts. The database constraint arbitrates the final write; the application handles waiting, conflict and rollback. Under Read Committed, DO NOTHING can skip insertion because of another transaction even when that row is not visible in the statement's snapshot; a following statement must inspect primary-database state.[3] Generating a fresh business key to make a retry succeed defeats the protection.

For Path B, serialize revision and conflict-state processing per business key, comparing the ledger and advancing current state within the transaction. Competing revisions 2 and 3 should leave revision 3, and revision 1 cannot downgrade it. Process multiple keys in a consistent order to reduce deadlocks. Do not wait for a model or human while holding locks; commit a blocked outcome and resume with a new approved action.

A partial unique index restricted to deleted_at IS NULL protects only the active subset. PostgreSQL supports precisely that conditional scope.[4] It can fit a contract allowing historical duplicates while keeping active rows unique, but permits a previously soft-deleted identity to be inserted again. If an old replay must never become a new sale, retain broader revision or receipt-identity controls instead of relying only on active-row uniqueness.

Partitioning, sharding, additional databases and bypass scripts need their own scope proof; a single-table result does not establish global uniqueness. Different sources may reuse order numbers under this example. Cross-source consolidation requires an explicit mapping contract, not quietly dropping source_system as an optimization.

Six failure tests with evidence and retest conditions

Test 1: resend R1 twice and reimport it under a different filename. Expect one current fact at 100 and no additional revision identity, although receipt records may increase. Inspect business keys and constraints, not generated row_id values. If duplication occurs, repair key scope and the final write path, then resend concurrently through two entrances to show filename independence.

Test 2: submit R4 and R5 alongside a line_no=0 control. If the enterprise approves zero as a valid identifier, retain it while quarantining NULL; otherwise report its separate domain violation. Never map both to zero. Inspect pre- and post-normalization values, quarantine reasons and NOT NULL enforcement. Retest missing, empty-text, zero and valid-positive inputs through the actual parser.

Test 3: deliver 100 and 120 at the same key and revision sequentially, then in one fresh batch. Expect a visible conflict, no silent overwrite and no automatic advancement while unresolved. Verify atomic ledger, gate and exception state. After repairing the path, first prove the block, then provide source adjudication, unblock and use R8 and R9 to verify the authorized correction to 90 without resurrection of the older version.

Test 4: deliver O7 line 1 for two tenants and O7 line 2 for T1. Preserve all three identities, totaling 240 in the initial stage. Inspect key sets rather than counts alone. On an incorrect merge, retain source evidence, repair the composite key and build an isolated replacement result. Verify individual amounts before cutover; manually fixing a total conceals lost facts.

Test 5: make two sessions meet at same-key insertion, then test both commit and rollback of the first transaction. After commit, the follower returns an existing outcome or controlled conflict; after rollback, it can continue with the original identity. There must not be two current rows. Check transaction order, returned state and primary records. If substituting MERGE, retest concurrency rather than assuming ON CONFLICT semantics.[3]

Test 6: leave duplicate target keys in a bulk candidate set, or start migration with conflicting legacy rows. Candidate classification or migration acceptance must prevent unsafe release. Check batch groups, exceptions and index validity. Resolve records from business evidence, retaining originals rather than arbitrarily deleting extras. Rerun all ten inputs and recovery replay to verify 230, three current facts and every unresolved identity exception.

Verify the constraint before optimizing throughput

Before adding uniqueness to an existing table, scan the proposed normalized key for missing fields, conflicting same-version content, cross-source conflation and soft-deleted history. Obtain business adjudication and compare an isolated candidate result. Coordinate writes during migration: a clean scan is only point-in-time evidence, and new conflicts can appear before cutover.

PostgreSQL concurrent index creation reduces blocking of normal writes but requires additional scans and waits. Failure can leave an INVALID index, and some failure stages can still affect uniqueness enforcement.[5] The presence of an index name is not completion evidence. Check the final command outcome, validity and actual counterexample inserts, then handle failed objects under the operational procedure instead of suppressing errors and opening the new write path.

At scale, monitor hot-key waits, batch conflicts, exception backlog, ledger size and recomputation duration. A full ledger has costs. Retain identity evidence through the approved replay horizon; older input should trigger reconstruction or review rather than automatically become new facts after ledger expiry. Measure wide-index maintenance against the target workload. Three illustrative facts cannot predict production throughput.

A reusable handover checklist records fact grain; the four-column scope; null and normalization policy; source revision evidence; equal-version conflict handling; current-state advancement; soft deletion and replay horizon; concurrency and migration outcomes; exception ownership; and final key sets and amounts. Deliver fixtures, expected outputs and real test records. These agreements and evidence make uniqueness a dependable boundary, rather than merely adding an object with unique in its name.

References

  1. [1] PostgreSQL 18 — Constraints (accessed 2026-11-14)
  2. [2] PostgreSQL 18 — INSERT / ON CONFLICT
  3. [3] PostgreSQL 18 — Transaction Isolation
  4. [4] PostgreSQL 18 — Partial Indexes
  5. [5] PostgreSQL 18 — CREATE INDEX
  6. [6] Debezium 3.7 — Exactly once delivery
Back to insights
鲁ICP备2024109755号-2
Drag to move. Right-click, touch and hold, or press Shift+F10 to choose a corner.