[dead]
Ask HN: Do you use foreign keys in relational databases?
21–30 of 251 posts
Re: Ask HN: Do you use foreign keys in relational databases?
#22In an OLTP system yes of course. In an OLAP system no since they’re often ignored anyway but are nice to be defined when trying to understand the schema. We logically use them in that the id column from says a supplier table might be found in the orders table.
Re: Ask HN: Do you use foreign keys in relational databases?
#23Yes I use constraints, because I like having CASCADE for DELETE and UPDATE.
I think trying to manage consistency in your application is probably a bad idea.
Re: Ask HN: Do you use foreign keys in relational databases?
#24Yes. I use FK constraints and cascades. Experience has taught me doing these things in the application layer is very lengthy to get right, often error prone, and rarely as fast.
Re: Ask HN: Do you use foreign keys in relational databases?
#25Why not just turn off foreign key checks during migrations and after validating the data, turn them back on? SQL Server lets you do this.
But you could get duplicate key errors when turning fk on. A customer of mine uses UUIDs as keys. That makes almost impossible to get duplicate keys and removes any problem with moving data from one db to another. Reading UUIDs is a minor pain though.
Re: Ask HN: Do you use foreign keys in relational databases?
#26I'm going to assume that by "foreign keys", you mean "foreign key constraints" where the DB itself is insisting on particular relationships. There are a few different schools of thought. I will list them, but the important thing to remember is not to be dogmatic. They are all right or wrong depending on your circumstance. One school of thought says "I want all data in my DB to be normalized. I want it to be right whe…
> At the volumes my organization works with, it is very difficult to write performant SQL queries that use JOINs and other relationships as a developer - even as a DBA! - and often much easier, for me, to write performant application code. How can this possibly be true? Won't that result in sending unnecessary data over the wire, stressing network and SQL buffer? What are these queries and what are these volumes? I j…
You made the parent commenter's point for them. You went over the heads of half the developers with join algorithms and index hints. That's just how it is, unless you're at a company with a very high bar for hiring and training.
Re: Ask HN: Do you use foreign keys in relational databases?
#27FK’s is what makes a relational database “relational”. It sounds like your colleagues want NoSQL (key-value) given their no JOINs policy.
"Relational" comes from https://en.wikipedia.org/wiki/Relational_model Table = relation
I followed the etymology further because that makes no sense on the face of it. It's referring to the relationship between columns. The table is a mapping from one column to another so there is a relationship between them.
This example makes it pretty clear: https://en.wikipedia.org/wiki/Finitary_relation#Example
I always thought it was referring to foreign keys too. Pretty bad name in hindsight.
Re: Ask HN: Do you use foreign keys in relational databases?
#28Besides the other reasons mentioned, FK's are also implicit documentation - even if you disable them.
Re: Ask HN: Do you use foreign keys in relational databases?
#29Why not just turn off foreign key checks during migrations and after validating the data, turn them back on? SQL Server lets you do this.
But you could get duplicate key errors when turning fk on. A customer of mine uses UUIDs as keys. That makes almost impossible to get duplicate keys and removes any problem with moving data from one db to another. Reading UUIDs is a minor pain though.
[deleted]
Re: Ask HN: Do you use foreign keys in relational databases?
#30I'm going to assume that by "foreign keys", you mean "foreign key constraints" where the DB itself is insisting on particular relationships. There are a few different schools of thought. I will list them, but the important thing to remember is not to be dogmatic. They are all right or wrong depending on your circumstance. One school of thought says "I want all data in my DB to be normalized. I want it to be right whe…
> At the volumes my organization works with, it is very difficult to write performant SQL queries that use JOINs and other relationships as a developer - even as a DBA! - and often much easier, for me, to write performant application code. How can this possibly be true? Won't that result in sending unnecessary data over the wire, stressing network and SQL buffer? What are these queries and what are these volumes? I j…
I'm not GP so I don't know what they meant, but a key upside to complex logic in the application layer vs the database is that the application layer is often much easier to scale out than the db. Where I work, if I run out of memory in the app I just change a configuration variable and k8s gives me more instances instantly. But if the database is memory constrained and I'm already on the biggest server available to me...I need to re-shard or take some other more sophisticated approach (tuning, replication, other stuff I don't know about).
Of course each scenario is different, YMMV, and as always "it depends".