question Foreign keys, yay or nay?
By today's standards, is there a value to using foreign keys beyond a safety net against developer error?
I have over 100 tables, and every site feature relies on joining 2 or more tables and matching up IDs. I'm debating on whether there's any benefit to creating foreign keys when the scripts are already developed and the only person that can ever touch them is me.
* CLARIFICATION: the only person that can touch the code and backend is me.
•
•
u/KeeperOfAngelsNorth 24d ago
The default answer should be: yes, always! They are designed to keep your data sane. It is impossible to anticipate every weird thing that might happen in a production environment.
They do add to the performance costs of inserts and updates The only reason that I would consider avoiding them would be in an insert heavy environment where the cost in terms of insert times is more than the application can endure. Even then, there are mature scaling options available that should be explored before you give up on that critical safeguard.
In one environment I worked many years ago, the cost of utilizing foreign keys was too great for our very busy insert-heavy application given the hardware and infrastructure constraints that we had at that time. Still, I integrated foreign keys into the model and required that developer, QA and staging environments used them even though they never got applied in production.
Don't think of developer error as a rare event which isn't going to happen. It will happen. What is even more likely is evolving requirements losing track of certain critical relationships that just break the model.
•
u/coworker 24d ago
FKs can't keep data sane. All they can do is ensure a row exists. They can't tell you that the right row exists (eg referencing an object this row shouldn't have access to)
•
u/KeeperOfAngelsNorth 24d ago
It doesn't guarantee that all aspects of the date are sane but, in a well designed model, it eliminates a large class of consistency errors which are otherwise very easy to make.
•
u/Mission_Pirate_4150 24d ago edited 24d ago
FKs help with
- Logic. I see then in a database, I understand things better.
- Performance. FKs should be indexed because they do help with performance in general.
- Development. They make development easier for me.
I’ve got a system I took over with no foreign keys. I’ve thrown every bit of duct tape to improve performance at it. It performs well enough. Admittedly, the choice of primary keys was horrible and that effects foreign keys eventually.
I’ve got multiple systems across multiple databases that I wrote with foreign keys that perform great.
This is my career finding. Fks are basics. Ignore them at your own peril is my view.
•
u/RainbowCrane 24d ago
Agreed.
And regarding performance, getting rid of foreign keys for a theoretical performance boost is the database equivalent of early code optimization: don’t start optimizing your database until you have query plans and runtime statistics that highlight performance bottlenecks. I’ve seen A LOT of bad database optimization that was carried out without data to back it up, and it mostly just results in a less supportable database
•
u/annebosch 24d ago
One more consideration, if I may. I agree that the default answer is "yes", and it may help you if you decide to use other tools in the future against that schema. For example, having the FKs defined helps AI agents figure out how your data relates. It helps for tools that create database diagrams also. And even if you are the only dev touching anything, the constraints may help prevent accidental deletes.
•
•
u/Lost_Term_8080 24d ago
What problem are you trying to resolve removing them?
At a high enough write rate to tables with foreign key constraints you will need to drop them to sustain the writes. In SQL Server this is several thousand writes per second to a table where it starts to become a problem. If you aren't at whatever write rate it is for MySQL, getting rid of them doesn't provide you with anything and makes testing code changes much more intensive to implement.
•
u/csdude5 24d ago
Not really trying to resolve a problem as much as prevent one in the future. I don't have FKs enabled right now, but I COULD enable them. I've already confirmed no orphan IDs, so all it would take is removing a few lines in existing code.
I'm just debating on whether it's a good idea.
Based on the replies so far I'm leaning towards "yes". I understand that there's a slight performance hit on inserts, but I think that would be negligible.
•
•
u/magicmulder 24d ago
Depends. Do your users typically delete a top level item and ask you to restore the 500 records connected to it across 10 tables? That's a nightmare. No FK = just restore that top level item.
•
u/punycat 24d ago
I worked with numerous large databases at a mega software company. Only one had foreign keys and it was a big pain to work with. Otherwise data integrity was enforced by the code and worked fine.
•
u/Stephonovich 24d ago
I also have worked with numerous large databases. (I’m a DBRE). The only one that didn’t have constant data integrity related incidents was the one using FK constraints. Everyone else seems to accept that as a valid trade-off for “velocity.”
•
u/CDavis10717 24d ago
Old, retired IT DBA Manager here, may I jump in on this topic?
ID with auto sequence is usually a db physical design decision and never appears in the logical design.
If you use ID auto-increment as primary key, the inserts will likely be physically ordered by ascending ID value.
The logical design primary key value, like unique account number, requires an index to enforce unique values. Fine, except are you tablespace scanning and sorting rows by account number every time?
Are rows deleted, leaving gaps in the ID values of the primary table. What’s the ID max value, like 2 billion? Will users key in a lot of digits to query a row? Will you need valuable screen space to show the ID values?
Considerations, that’s all.
•
u/bippy_b 24d ago
So .. if you have people going in and making manual updates… then absolutely foreign keys need to be in place to help prevent mistakes.
But if it is all system entered data.. they can get in the way.
One of my previous jobs, we made probably 400-500 database updates a week manually (because a doctor/nurse accidentally pressed a 4 on the telephone instead of a 1… or they were entering the patient data via web form and went to press 9 and pressed 0 accidentally. Clinical trials.. what are you gonna do? The doctors/nurses just are trying to enter the data quickly so they can go home or progress in the surgery. )
•
u/mabhatter 24d ago
I come from the land before everyone started making dumb number only ID fields. Every file had to have a specific key or composite key in order to function. It also made it really easy to look at the file descriptions and tell from the keys what other files it was supposed to match to and what fields were important.
Technically in the world of proper 4n databases you shouldn't have that much redundant data in your database schema. But it's still good to actually design your tables with an actual key made from actual data in mind and not just ID numbers. It makes it way easier to understand what your table is supposed to actually do two years from now and prevents data from being duplicated in a table that shouldn't be because the database will kick an error for you.
I have a love-hate relationship with ID columns. They are obviously required if you're doing a truly 4n database with the absolute minimum of data duplication, but they're absolutely terrible when you're trying to understand a database to build reports over and only have minimal information on what the tables do.
•
u/Stephonovich 24d ago
Agreed that natural keys are superior, except for performance at scale, and by scale, I mean a minimum of hundreds of millions of rows. I’ll take integers all day long then, thanks.
•
u/dschledermann 24d ago
I'm going to say nay. I work in a telephone company where the amount of data can be quite unwieldy, and I have to say that foreign keys are an absolute pain.
If you have a small-ish application with only a single application accessing the database, I guess it can be a nice extra data integrity to have foreign keys, even if it comes with some extra cost and limitations.
If, however, you have massive amounts of data, unclear data life cycles, multiple applications using the database, etc, etc, using foreign keys is a non-starter.
•
u/Several9s 13d ago
Foreign keys are there for the purpose of referential integrity. It is a big problem, especially in relational database and OLTP. OLTP is a workload. Relational is a data model. MySQL is a relational database that is almost always used as an OLTP store. In that regard, foreign keys help data remain consistent and retain data integrity as data goes big and bigger, complexity also rises.
Now the question is how you design your data. It’s not a question if you have to use foreign keys or not. It should be, how to design your schema to fit your needs. Other than that, optimization, query tuning, and database tuning are one of the things you have to deal with as well.
On the other hand, without using foreign keys, obviously, you’ll gain speed. But what cost you might have it? If you do not need referential integrity and especially if data is not sensitive to constraints, then feel free to design your table without it. Others, do denormalized table to avoid joins table but in the cost of data duplication. Still doable but as mentioned, on how you design your table or schema.
There are tons of benchmarking models regarding this which you can find it in Google or search engines but still, in MySQL/MariaDB/Percona Server, it has been kept improving and resources aren’t as expensive compared in the past. There are variables or parameters certainly you can configure to tun, ensure you have big reserve for innodb_buffer_pool_size, set proper timeout for innodb_lock_wait_timeout and let it fail if stucks, set proper value for innodb_flush_log_at_trx_commit if fsync is a problem, or set innodb_autoinc_lock_mode to 2 making sure interleaved with binlog_format=ROW.
•
u/Hacym 24d ago
Just wait until you’re not able to cascade a delete and you’ll wish you had them :)