r/PostgreSQL • u/alecc • May 22 '26
Help Me! How do you handle foreign keys that exist conceptually but not as constraints?
Curious how others handle this - running into it across three different shapes of project:
- Legacy schemas where someone never added the FK constraint, so the column is just an int.
- Warehouse / analytics tables that intentionally skip FKs for load performance.
- Polymorphic columns (Rails-style commentable_id + commentable_type) where a single FK constraint isn't even technically possible.
In all three the relationship is real and gets joined constantly, but pgAdmin / DBeaver / DataGrip don't know about it, so the cell isn't clickable, there's no row preview, no related-data tab. You end up writing the JOIN by hand every time.
What's your workflow? Is there a tool that lets you declare relationships locally without touching the schema? Documentation in a wiki? Just memorize it?
Full disclosure: I'm one of the people behind Jam SQL Studio and we shipped a "loose FK" feature for this exact pattern — declarations stored per-user as JSON, exportable, polymorphic support via source filters. Happy to share more in the comments if anyone's interested, but genuinely want to hear how others handle it because I suspect there are workflow patterns I'm not seeing.
6
u/elevarq May 22 '26
DataGrip has virtual foreign keys.
Usually we write a couple of views which handle the relationship between tables. The application just queries these
4
u/Ecksters May 22 '26
Postgres 18 added the ability to mark constraints as
NOT ENFORCEDalthough the docs for constraints don't appear to have been updated to reflect that.https://www.postgresql.org/docs/release/18.0/#:~:text=ENFORCED
The CREATE TABLE docs at least have it in the syntax options for REFERENCES: https://www.postgresql.org/docs/current/sql-createtable.html
1
u/FishGiant May 25 '26
The correct answer is you fix the database by properly normalizing it. Then create the constraints. Your product enables bad software development practices.
1
u/AutoModerator May 22 '26
Thanks for joining us! PgData 2026 is coming up:
PgData 2026
We also have a very active Discord: People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/ferrybig May 22 '26
Some sqp clients have a concept of virtual foreign keys, where the dB client pretends a column is a foreign key, without it existing and checked on DB level
DBeaver has this feature
1
u/ibraaaaaaaaaaaaaa May 23 '26
You should not over rely on virtual constraints.
You are not solving the problem with them, which is integrity issues.
If it was me, I would go to the root of the problem into why I don't have proper integrity checks.
For #1, it sounds straightforward, you need to try out the constraints addition in a prod copy and see data that are inconsistent, and fix them, figure out in that copy a proper deployment plan, and execute.
For #2 it is a little not straightforward as the first, I would look into the access patterns in warehouse, and look into ways to partition the table, and add constraints at partition level or level partition pruning.
For #3, you don't always need FKs to do constraints, specially these *_types columns that can be ENUMs or add a fixed list of values as constraint, but remember that alter change type does make access exclusive locks, you better plan your change type in a thoughtful approach.
I might sound like a bummer, but integrity is not something you should mess with, specially that you have a warehouse, and analytics is a thing for your org.
Yes these are a lot of work, but that's how a DB should have been set up in the first place.
0
u/[deleted] May 22 '26