# Should imported records use the external provider's identifier as the application's primary key? > Use your own stable primary key for imported business records, and treat provider IDs as namespaced external keys with explicit merge and rekey rules. Canonical URL: https://www.devobs.io/articles/qa-external-id-mapping/ By: Owen Park Published: 2024-10-07T10:09:53.978Z Updated: 2026-09-06T08:31:04.426Z Section: Architecture Use your own primary key. For imported business records, the safer contract is an internal immutable ID for the record itself, plus one or more external identifiers stored separately as namespaced unique keys such as `(provider, external_id)`. That design survives provider rekeys, lets one record map to multiple upstream systems, and keeps foreign keys stable. Using the provider’s ID as your primary key is only reasonable when that provider is the sole long-term system of record and rekeys are contractually impossible. ## Why not make the vendor ID your primary key? A primary key becomes the anchor for foreign keys, cache keys, URLs, audit logs, and deduplication rules. PostgreSQL’s documentation is the practical reminder here: a primary key identifies a row and is the usual target for foreign keys, while `UNIQUE` constraints are available for alternate identifiers ([PostgreSQL constraints documentation](https://www.postgresql.org/docs/current/ddl-constraints.html)). That is exactly the split you want. External identifiers are integration facts, not your record’s identity. Providers rename tenants, migrate data, merge accounts, and occasionally reissue identifiers. If you use the vendor ID as your primary key, every rekey becomes either a cascading rewrite or an awkward “delete and recreate” event that breaks history. For the internal key, a UUID is a good default in distributed systems because it “requires no central registration process” and works well as a durable identifier across boundaries ([RFC 9562](https://www.rfc-editor.org/rfc/rfc9562.html)). If index locality matters, prefer a time-ordered UUID variant supported by your stack rather than random UUIDv4. ## What should the data model look like? Model the business record and the external mappings separately. Worked example: one product exists in ERP and in Shopify. - `products.id` = your immutable internal UUID primary key - `products` holds your canonical fields and lifecycle state - `product_external_keys(product_id, provider, external_id, status)` - unique constraint on `(provider, external_id)` - foreign key from `product_external_keys.product_id` to `products.id` This lets one internal product map to `('erp', 'A-1049')` and `('shopify', 'gid-7781')` at the same time. The row in `products` is the thing your application owns; the external keys are evidence about where that thing appears. ## How should rekeys, merges, and reimports behave? Treat provider rekeys as updates to the mapping table, not as new business records. The enforced boundary is important: first claim or create the `(provider, external_id)` mapping inside one database transaction, then write the canonical record changes that depend on that mapping. Do not rely on a check-then-write sequence outside the database. For merges, pick one surviving internal record, move external mappings and dependents to it in one transaction, and mark the losing record as merged rather than hard-deleting it immediately. `CHECK` constraints are not the right tool for cross-row identity rules; PostgreSQL explicitly recommends `UNIQUE` and `FOREIGN KEY` constraints for cross-row and cross-table restrictions ([PostgreSQL constraints documentation](https://www.postgresql.org/docs/current/ddl-constraints.html)). For deletion and reimport, separate “not currently present upstream” from “new identity.” If a provider deletes and later reuses the same external ID for a different product, your import needs provider-specific evidence before reattaching that mapping. Keep tombstones or import history so reimport does not silently resurrect the wrong row. ## When is using the external ID as the primary key acceptable? It can be acceptable for a narrow integration service when all of these are true: - exactly one upstream provider exists - that provider is the permanent system of record - the identifier is documented as immutable - no other provider will map to the same record - you can tolerate downstream key churn if the assumption fails If any of those conditions look temporary, use an internal key now. ## What decision checklist should you apply? Ask: 1. Can one record appear in more than one external system? 2. Could the provider ever rekey, merge, split, or recycle IDs? 3. Do internal references need to outlive a specific integration? 4. Will you need [soft-delete](https://www.devobs.io/articles/soft-delete-integrity-traps/), reimport, or audit history? If you answered yes to any of them, keep the provider ID out of your primary key. ## Follow-up: how do I prevent duplicate imports? Use a unique constraint on `(provider, external_id)` and perform mapping creation in the same transaction as the import decision. That gives you an enforced boundary, not just a preflight lookup. ## Follow-up: should I ever expose the internal ID externally? Yes, if clients need a stable application identifier independent of vendor choice. Expose the internal ID as your API handle, and expose external IDs only as integration metadata. Reviewed: 2026-09-05 ## Source references - -