What does a foreign key guarantee, and what do you lose with database per service?

A foreign key looks like one constraint, but it bundles several guarantees. When you split databases, you need to know which of them you are giving up.

  • Existence at write time. You cannot insert an order whose customer_id has no matching row. The check runs inside the same transaction as the insert, so a concurrent delete cannot slip in between the check and the write.
  • Delete behaviour. ON DELETE RESTRICT blocks removing a referenced customer. CASCADE removes dependent orders in the same transaction. SET NULL detaches them.
  • Atomicity. The referencing write and the referential check succeed or fail together, so no observer ever sees an orphan.

With database per service, customers and orders live in different databases. No engine can enforce a constraint across them. Each guarantee becomes your application's job.

Guarantee Shared database Database per service
Referenced row exists on insert Enforced by the engine Must be checked by a call or against a local projection, and the result can be stale
RESTRICT on delete Enforced by the engine Owner must ask dependents, or adopt soft delete
CASCADE on delete Same transaction Asynchronous, driven by events
No observable orphans Always true Orphans can exist until reconciled (eventual consistency)

Don't rebuild all four by reflex. For each reference, ask what goes wrong if it dangles for a few seconds, or permanently. An order pointing at a deleted customer may be acceptable history. A payment pointing at a nonexistent account probably is not. Rebuild only the guarantees whose loss would cause a real failure.

-- Shared database: one constraint bundles three guarantees.
CREATE TABLE customers (
  id   bigint PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  -- 1. Existence: the insert fails if customer 42 does not exist.
  -- 2. Delete behaviour: RESTRICT blocks deleting a referenced customer.
  -- 3. Atomicity: the check runs in the same transaction as the write.
  customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  total_cents bigint NOT NULL
);

INSERT INTO orders (id, customer_id, total_cents) VALUES (1, 42, 1999);
-- ERROR: insert or update on table "orders" violates foreign key constraint

-- Database per service: orders_db cannot reference customers_db.
-- The column survives; every guarantee above is now application code.
CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id bigint NOT NULL,  -- plain value, no REFERENCES clause
  total_cents bigint NOT NULL
);

-- This succeeds even if customer 42 never existed or was deleted a moment ago.
INSERT INTO orders (id, customer_id, total_cents) VALUES (1, 42, 1999);

The same orders table with and without a foreign key, showing that after the split the engine accepts an order for a customer that may not exist.

Who owns the data? Ownership and identifiers across service boundaries

Before you can decide which guarantees to rebuild, you need to know which service is responsible for them. The rule is simple: every entity has exactly one owning service. Only the owner writes to it, decides what a valid state looks like, and announces changes. Every other service holds one of two things: an opaque identifier, or a derived copy that it treats as read-only and replaceable.

The identifier you pass across that boundary matters more than it seems. Natural keys such as email addresses or account numbers look convenient because they already exist. They also change and get reused. A customer updates their email, or a closed account number is reassigned to a new customer. Every downstream service storing the old value now points at the wrong entity or at nothing, and nothing tells it so. A globally unique, immutable ID such as a UUID avoids this. The owner generates it once, never changes it, and never reuses it, so a reference stays correct even after every other attribute changes. Consumers should treat the ID as opaque and avoid parsing structure or meaning out of it.

This discipline applies to teams running independently deployed services against separate databases. If services share a schema or always deploy together, the cost buys little.

Unclear ownership is the root cause of most cross-service integrity bugs. When two services both write customer status, or nobody owns deletion, no amount of validation or event plumbing will produce a consistent answer.

How do you validate a cross-service reference at write time?

Once ownership is settled, the consuming service still has to decide, when an order arrives with a customer_id, whether that customer exists. There are two workable answers.

Synchronous existence check. Before committing, call the owner's API. This rejects typos, IDs from another environment, and references to customers that never existed. It does not close the race. The customer can be deleted a millisecond after the check returns, so you still need lifecycle handling (section 5). It also couples availability: if the customer service is down, order writes fail. Caching positive lookups reduces that coupling but widens the stale window. Failing open, by accepting the write when the owner is unreachable, keeps writes flowing but means the check sometimes does nothing.

Local reference projection. Consume the owner's events into a small table containing customer_id, status, display_name, and version. Validate and join against it inside your own transaction. The lag between the owner's commit and your projection update can be reduced but not eliminated, so a just-created customer may briefly look unknown. Decide whether to reject, retry, or park those writes.

The consumer must be idempotent. Each event carries the entity's monotonically increasing version. Apply it only if event.version > stored.version, using a conditional upsert. Duplicates and late arrivals become no-ops, and a full replay converges to the same state.

Concern Synchronous check Local projection
Staleness Check-to-commit race Event lag window
Owner outage Writes fail (or fail open) Writes continue
Local joins No Yes
Extra machinery Client, timeouts, cache Consumer, replay, versioning
-- PostgreSQL. Minimal local projection of customers owned by the customer service.
CREATE TABLE customer_projection (
  customer_id  uuid   PRIMARY KEY,
  status       text   NOT NULL,  -- 'active' | 'suspended' | 'deleted' (tombstone row, never hard-delete)
  display_name text   NOT NULL,
  version      bigint NOT NULL   -- owner's per-entity, monotonically increasing version
);

-- Consumer: run once per event. $1..$4 come from the event payload.
-- The WHERE on DO UPDATE makes this idempotent: duplicates, out-of-order
-- events and full replays only ever move a row forward in version.
INSERT INTO customer_projection (customer_id, status, display_name, version)
VALUES ($1, $2, $3, $4)
ON CONFLICT (customer_id) DO UPDATE
  SET status       = EXCLUDED.status,
      display_name = EXCLUDED.display_name,
      version      = EXCLUDED.version
  WHERE customer_projection.version < EXCLUDED.version;
-- 0 rows affected = stale or duplicate event: acknowledge it and move on.
-- Deletes arrive as status = 'deleted' with a higher version, so a late
-- 'updated' event cannot resurrect the customer.

-- Order service: validate the reference locally, in its own transaction.
BEGIN;
INSERT INTO orders (order_id, customer_id, total_cents)
SELECT $1, c.customer_id, $3
FROM customer_projection c
WHERE c.customer_id = $2
  AND c.status = 'active';
-- 0 rows inserted: customer unknown (possibly projection lag) or not active.
-- The application decides whether to reject, retry later, or park the write.
COMMIT;

A PostgreSQL customer projection maintained by a version-guarded conditional upsert, so duplicate, late and replayed events are no-ops, and an order insert that validates the customer reference against that local table.

How does the transactional outbox keep projections reliable?

The projection approach in the right-hand column only works if events reliably arrive. The obvious implementation commits the business row and then publishes to the broker. That is a dual write: two systems with no shared transaction. If the process crashes after the commit but before the publish, the event is lost and the projection silently diverges. If you publish first and the commit then fails, consumers act on a change that never existed. Retries do not fix either ordering. They only shrink the window.

The transactional outbox takes the second write out of the request path. The owner service inserts the business row and an event row into an outbox table in the same local transaction, so both commit or neither does. A separate relay reads committed outbox rows, either by polling or by tailing the database's change log. It publishes each row and then marks it as sent.

 Owner service
 ┌───────────────────────────┐
 │ BEGIN                     │
 │   UPDATE customers ...    │
 │   INSERT INTO outbox ...  │
 │ COMMIT                    │
 └────────────┬──────────────┘
              │ committed rows
              ▼
         ┌─────────┐   publish   ┌────────┐   consume   ┌──────────────────┐
         │  Relay  ├────────────►│ Broker ├────────────►│ Consumer service │
         └─────────┘             └────────┘             │ local projection │
                                                        └──────────────────┘

The relay can crash between publishing and marking the row, so delivery is at-least-once. Consumers must be idempotent. Two common ways to do this are recording processed event IDs and applying versioned upserts that ignore older versions. Ordering holds only per partition, so key events by the aggregate ID.

Operational cost

The pattern adds several pieces you have to run and maintain:

  • A broker to operate.
  • A relay whose lag you must monitor.
  • An outbox table that needs pruning.
  • Event schemas that must evolve compatibly, because old events get replayed.
  • Replay tooling to rebuild projections.

For a few services with modest write volume, synchronous validation or a shared database may be the cheaper choice.

-- Owner service: outbox lives in the same database as the business table.
CREATE TABLE outbox (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  aggregate_id bigint      NOT NULL,
  event_type   text        NOT NULL,
  payload      jsonb       NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  sent_at      timestamptz            -- NULL until the relay has published it
);

-- Business change and event commit together, or not at all.
BEGIN;
UPDATE customers SET email = 'new@example.test', version = version + 1
  WHERE id = 42;
INSERT INTO outbox (aggregate_id, event_type, payload)
  SELECT id, 'CustomerEmailChanged',
         jsonb_build_object('id', id, 'email', email, 'version', version)
  FROM customers WHERE id = 42;
COMMIT;

-- Relay (polling variant): claim a batch without blocking other relay workers.
BEGIN;
SELECT id, event_type, payload FROM outbox
  WHERE sent_at IS NULL ORDER BY created_at LIMIT 100
  FOR UPDATE SKIP LOCKED;
-- ...publish each row to the broker; a crash here causes redelivery...
UPDATE outbox SET sent_at = now() WHERE id = ANY($1);  -- $1 = published ids
COMMIT;

-- Consumer: versioned upsert, so duplicates and stale events are no-ops.
INSERT INTO customer_projection (customer_id, email, version)
VALUES ($1, $2, $3)
ON CONFLICT (customer_id) DO UPDATE
  SET email = EXCLUDED.email, version = EXCLUDED.version
  WHERE customer_projection.version < EXCLUDED.version;

A PostgreSQL sketch of the outbox pattern: the business update and outbox insert share one transaction, a polling relay claims unsent rows with FOR UPDATE SKIP LOCKED and marks them sent after publishing, and the consumer applies a versioned upsert so at-least-once redelivery is harmless.

What replaces ON DELETE CASCADE? Lifecycle semantics and a worked example

Once each service owns its tables, no database can enforce a delete rule across them. The owner instead models deletion as a lifecycle: it marks the row deleted (a soft delete), keeps a tombstone holding only the ID and deletion time, and publishes a CustomerDeleted event. Each consumer then decides what that event means for its own data.

Consumer policy Behaviour Typical use
Block Owner checks dependents before deleting Deletion with active obligations is invalid
Cascade-by-event Consumer deletes or closes its own rows Carts, sessions, preferences
Orphan-and-flag Keep rows and mark the reference dangling Reporting, later manual review
Retain Keep data despite the delete Invoices, audit trails, legal holds

Here is the worked example. A customer with two open orders asks to close their account. The Customer service queries Orders, which reports open orders, so the request moves to PENDING_CLOSURE instead of being deleted. When the orders ship or are cancelled, the deletion proceeds. Orders then keeps its invoices under the retain policy and erases the copied name and address. Every service that cached personal data has to honour erasure, so the deletion event serves as a privacy obligation as well as a cleanup hint. Duplicated projections also take up storage until consumers purge them.

A flow like this, spanning several services, is a saga. In an orchestrated saga, a coordinator issues each step and tracks its state. In a choreographed saga, each service reacts to events, which avoids a central component but makes the overall flow harder to trace. When a step fails, the saga runs compensating actions. Compensation is not rollback. Other services may already have seen the intermediate state, and some effects cannot be reversed. An email that has been sent stays sent, and the most you can do is send a correction.

-- Customer service (owner): runs only after the closure check passed.
-- Soft delete, tombstone and event are written in ONE local transaction
-- (transactional outbox), so the event is published only if the delete commits.
BEGIN;
UPDATE customers
   SET status = 'DELETED', deleted_at = now(),
       name = NULL, email = NULL, address = NULL   -- owner erases its own PII
 WHERE id = '7f3c2a10-0000-4000-8000-000000000001'
   AND status = 'PENDING_CLOSURE';                -- no open orders remain

INSERT INTO customer_tombstones (customer_id, deleted_at)  -- ID and time only
VALUES ('7f3c2a10-0000-4000-8000-000000000001', now());

INSERT INTO outbox (event_id, event_type, aggregate_id, payload)
VALUES (gen_random_uuid(), 'CustomerDeleted',
        '7f3c2a10-0000-4000-8000-000000000001',
        jsonb_build_object('customerId', '7f3c2a10-0000-4000-8000-000000000001'));
COMMIT;

-- Orders service (consumer): applies its own policies to the event.
BEGIN;
-- Idempotency: the handler checks the row count and stops if this is a redelivery.
INSERT INTO processed_events (event_id)
VALUES ('c1d2e3f4-0000-4000-8000-000000000042')
ON CONFLICT (event_id) DO NOTHING;

-- Cascade-by-event: carts have no value once the customer is gone.
DELETE FROM carts WHERE customer_id = '7f3c2a10-0000-4000-8000-000000000001';

-- Privacy: erase the copied personal data in the local projection.
UPDATE order_customer_snapshot
   SET name = NULL, shipping_address = NULL, erased_at = now()
 WHERE customer_id = '7f3c2a10-0000-4000-8000-000000000001';

-- Retain: invoices stay, keyed by customer_id only, for legal and audit reasons.
COMMIT;

A PostgreSQL sketch of the owner soft-deleting a customer, writing a tombstone and a CustomerDeleted outbox event in one transaction, and the Orders service handling that event by cascading carts, erasing copied personal data and retaining invoices.

How do you detect and repair drift between services?

Every mechanism described so far can fail quietly. A consumer can miss an event, or a bug in the owner can skip an outbox write. Someone can run a manual fix against one database and not the others. Without detection, the first sign of drift is usually a customer-facing error. An engineer then spends hours tracing one bad row through logs from three services.

A reconciliation job catches this systematically. On a schedule, it reads the identifiers a consumer holds and checks them against the owner's current state, either through the owner's API or a bulk export. Each mismatch is recorded with a category, such as "references a deleted entity", "references an unknown ID", or "projection field out of date", so that the fix matches the cause.

Metrics worth tracking

  • Orphaned reference count per consumer and per relationship. A steady non-zero baseline points to a systematic bug. A sudden jump usually means a deploy or migration went wrong.
  • Projection lag: the time between the owner committing a change and the consumer applying it. Lag explains transient mismatches, so the job should ignore records that changed inside the expected lag window.
  • Repair outcomes: how many mismatches were fixed automatically and how many needed a person.

Repair has to be rehearsed

Detection is useful only if there is a defined repair path that the team has actually run. Typical repairs are replaying events from a known offset, re-fetching an entity, or applying the agreed lifecycle rule to orphans. Test these repairs in staging before you need them in production.

Costs and limits

Full scans of large tables are expensive for both sides. Common ways to reduce the cost are comparing checksums over ID ranges, sampling, and checking only rows that changed since the last run. Reconciliation is also inherently after the fact, so it limits how long drift persists but does not prevent it.

When should you avoid this approach, and what are the alternatives?

All of that machinery is worth building only when the boundary is right. If an order cannot exist without a consistent customer record, if you keep adding compensations for a single invariant, or if most features change both services together, the boundary is probably in the wrong place. Merging the two into one service restores a real foreign key and a single transaction. That is usually cheaper than another reconciliation job.

The opposite mistake is splitting too finely. Each cross-service reference needs validation, a projection, lifecycle rules and drift checks. Ten tiny services with dense references can cost more to maintain than the independence they provide. Each one also adds a failure mode that nobody owns.

Option Integrity Autonomy Development cost Operational load
Shared database Strong (real FKs) Low: schema changes couple teams Low Low, but one shared blast radius
Read-only shared reference schema Strong for reference data Medium Low Low to medium
Merge services Strong Lower for the merged pair Low Low
Synchronous checks only At write time only Medium: runtime dependency Medium Medium, latency and availability coupling
Event-driven projections Eventual High High High: outbox, consumers, reconciliation

When each option fits

  • Shared database: fits one team that owns several deployables, as a transitional step.
  • Read-only shared reference schema: fits slow-changing lookup data such as currencies or country codes, where one owner writes and everyone else reads.
  • Merge services: fits when the invariant is transactional by nature.
  • Synchronous checks: fit low-volume writes where staleness is unacceptable and you can tolerate the dependency.
  • Event-driven projections: fit when teams must deploy independently and the domain can tolerate eventual consistency.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to database per service foreign keys, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.