Access a foreign key from another service in database migrations

Feedback & Ideas
1

How do you write a database migration to reference another table in another service as a foreign key access constraint.

CREATE TABLE tasks (
  id              UUID NOT NULL PRIMARY KEY,
  uid            UUID NOT NULL REFERENCES `users(id)` ON DELETE CASCADE
}

where uid is a foreign key from the table users in the users service.

2

Unfortunately that’s not something that you can do. Postgres doesn’t support foreign key references across databases. If you want to use foreign keys the tables need to be inside the same database.

When building distributed systems you typically accept that you get weaker guarantees when it comes to foreign key constraints. For most applications that trade-off is worthwhile.

1 Like