Primary key collision prevention across database nodes and shards.

How primary key collisions happen

Example: Node A and Node B both generate integer ID 5001 during a network delay. Each insert succeeds locally. When replication or consolidation occurs, one change conflicts or the system contains two logically incompatible records.

Collisions also occur outside the primary key. Email addresses, order numbers, external references, invoice numbers and idempotency keys may require global uniqueness.

Common identity strategies

The ShardronDB PK Guard rule

ShardronDB’s public architecture applies a strict boundary to normal client inserts:

Client INSERT request
        ↓
Read table metadata and identify the real primary key
        ↓
Does payload contain the PK column?
    Yes → reject: CLIENT_PK_NOT_ALLOWED
    No  → continue
        ↓
ShardronDB generates and coordinates identity
        ↓
Route write to the owning node

The rule uses metadata rather than assuming every table uses a column named id. A table may use customer_id, order_key or another primary key. The protection must inspect the actual schema.

This rule has two goals: prevent accidental or malicious bypass of the allocation policy, and keep the authority for distributed identity inside the trusted system boundary.

Trusted internal flows

Backup restoration, migration, replication apply and deterministic recovery often need to preserve existing identifiers. These operations should not weaken the public gateway rule. They should use separate trusted paths with explicit authorization, audit logging and source mapping.

• normal client/API insert: PK prohibited;
• internal replication apply: PK allowed only under trusted protocol;
• backup/recovery apply: original PK preserved with controlled validation;
• data migration: deterministic mapping and collision report required;
• test fixtures: isolated environment or trusted import path.

Primary keys are not the only invariant

A generated technical ID can be unique while the business operation is duplicated. Payment requests, order submissions and lead imports need idempotency keys or natural-key checks. Inventory and account limits may require transaction coordination across several rows.

Define uniqueness at three levels:

1. technical identity: every stored row has a unique primary key;
2. business identity: a real-world entity is not inserted twice;
3. operation identity: retries do not repeat an effect.

Index and performance considerations

Identifier selection affects B-tree locality, index size, cache behavior and storage. Sequential integers are compact and insertion-friendly but need coordination across writers. Random UUIDs distribute generation but may fragment indexes. Time-ordered UUID variants and ULIDs improve locality while preserving distributed generation, although engine-specific benchmarking is still necessary.

Collision testing checklist

• issue concurrent inserts from every writable node;
• repeat requests with the same idempotency key;
• simulate allocator unavailability and clock skew;
• rejoin a node with stale sequence state;
• import a backup containing existing identifiers;
• change ownership or add a new shard;
• attempt client payloads containing the real PK under alternate field names;
• verify logging and error codes for rejected requests.

Related architecture topics

Identity design is closely connected to database sharding, multi-primary replication and gateway-controlled coordination. The ShardronDB Layer 1 architecture page explains how the guard fits into the public write path.

Frequently asked questions