51–60 of 251 posts
Performance can be another reason for skipping them but modern dbs are pretty good.
Well, one might ask, what is the point of having foreign keys if they are disabled? And the answer is, there are several benefits. Here are a couple:
1. foreign keys, disabled or not, create a record of your data design that itself serves as documentation, and that can be programmatically queried, extracted, copied, modified etc, all of which reduces technical debt and is useful for other users or developers to understand your schema and work more efficiently.
2. You still have the option to enable the foreign keys. Furthermore, you can pass this job over to someone in your organization who may not have authority to create or modify foreign keys, but does have the authority to enable them and/or to fix the data however required to enable them
Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them. Another classic is the “join…
How about when the ID in a FK column has been generated outside the RDBMS but the target of the ID has not been written yet?
I'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…
Also some ORMs write dreadful SQL where it comes to joins
A badly written join (or collection of joins) will take a longer time to run that will, when the system is under load, backlog other queries. If these errant queries make up a significant portion of your queries then it will hit performance significantly
It's not the joins themselves just the incorrect use of them
Yes. 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.
Even if it was easy to get things right at the application layer, in a legacy system (and today's modern hotness is tomorrow's legacy system), the database is a constant. Entire generations of application may rise and fall. New languages, frameworks, developers all lead to rot over time. Heck, some legacy projects the application code is incomplete or lost. But the database doesn't rot. Show me a database that is 20…
Commenting just to draw attention to your comment.
Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them. Another classic is the “join…
I recently migrated to EntityFramework Core (from the non-core version) and I’m actually impressed. Most SQL is pretty much what I’d write by hand.
Now granted, if there are complex joins, subqueries and stuff, I don’t even try wrangling the ORM to somehow give me that output, but still. I feel more comfortable just using EF than I used to.
He's talking about FK constraints right? We do not use them for write performance concerns. There's plenty of nice features of the DB we're not allowed to use under the excuse of "performance". But I'm told this by people who live and breathe SQL, so I trust them and I hope they have evidence to back it up. Because a lot of these features we're not allowed to use would make our lives 100x easier if we could!
In this talk, we start at 10,000 transactions per second, and just by altering the design we get to 20,000 transactions per second... all on a 5 year old laptop.
And at no time did we ditch any constraints (primary or foreign). The claim that you can't get performance with constraints on a database is a myth
For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted.
I always FK a large table (millions or more rows) to a small table (tens to hundreds of rows).
But I will pause and ask hard questions about FK a large table to a large table... will this impact migrations? Do I need this FK? Is data integrity at risk without this FK even assuming a buggy app? Does the app utilise this FK for queries, or is there zero performance benefit from having the FK? If I don't have the FK are both tables always queryable by a PK? Should I have an index, potentially a UNIQUE index, in place of a FK?
Like most things... it depends. A dogmatic insistence on using them everywhere isn't always healthy, and the inverse is true that an avoidance everywhere isn't healthy.
The DB is there to store data and make it available, whilst enforcing data integrity... if it makes sense to use a FK to achieve those things do it, otherwise don't.