Ask HN: Do you use foreign keys in relational databases?

188 points by frogcoder 3 years ago | 244 comments

I use foreign keys quite often in my schemas because of data integrity, while my colleague has a no FK policy. His main argument is difficulties during data migrations which he frequently encounters. He rather have a smooth data migration process than having an unexpected error and abort the whole operation that the migration is only a small part of. I suspect the errors might be mainly caused by not considering data integrity at all at the first place, but I can feel his pain. To be fair, as far as I know, he never had major data problems.

He is not the only one I've met who insisted on not having FK. I've even seen large systems prohibit using JOIN statements.

Personally, I see the data integrity out weights the inconveniences, do you use FK for your systems, what are your experiences?

mtts 3 years ago |

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 “joins are slow” argument, which I believe goes back to a period in the late 1990s when in one, not highly regarded at the time, database, namely MySQL, they were indeed slow. But the reason “everyone” knew about this was precisely the oddness of this situation: in fact RDBMSes are highly optimized pieces of software that are especially good at combining sets of data. Much better than ORMs, anyway, or, god forbid, whatever you cobble together on your own.

There is, in my mind, only one valid reason to not use foreign keys in a database schema. If your database is mostly write only, the additional overhead of generating the indexes for the foreign keys may slow you down a little (for reading, these very same foreign keys in fact speed things up quite considerably). Even in such a case, however, I’d argue you’re doing it wrong and there should be a cache of some sort before things are written out in bulk to a properly setup RDBMS.

Semaphor 3 years ago | |

> Much better than ORMs

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.

moonchrome 3 years ago | | |

My main problem with Entity Framework is the magic underneath.

Like simple operation

    x = Ef.Find(xid)
    x.Name = "something"
    y = Ef.Find(xid)

what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ?

Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added as you're firing off other related queries, until eventually it does not (usually in production only).

It's a really really complex system designed to look simple and pave over important details with "works most of the time" defaults.

tailspin2019 3 years ago | | |

Another vote for EF Core here. It’s superb.

AdamN 3 years ago | | |

ORM is a very valuable tool and should be aggressively used. One can always step down to SQL as needed but otherwise, the ORM logic is easier to write and maintain.

rowanG077 3 years ago | | |

Yep entity framework is truly amazing. If you have used that ORM you never go back. You still need to sometimes make your own query for perf or other needs. But it's quite rare in my experience.

Most of the time when I had performce issues it isn't EF. It's a missed index or higher level query issue.

sonthonax 3 years ago | |

> Another classic is the “joins are slow” argument

The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.

AdamN 3 years ago | | |

I remember getting beers with somebody in the aughts who claimed that he saw an entire website where the url was the key and the webpage was the value in an Oracle database. Any code was SQL operations inside the value field.

10x-dev 3 years ago | | |

Are joins in a 5NF database now as fast as querying a denormalized database?

lupire 3 years ago | | |

To be fair thata a reasonable approach if the database is at its monolithic scaling limit in CPU but not IO, while the clients can scale horizontally to more machines.

Unlikely in practice, though.

tluyben2 3 years ago | |

We used to do large setups at companies for what was then called intra and extranets begin 00s. These were very read/write intensive as the staff and partner staff would be on there basically all the time during office hours and data was not great for caching as data changed a lot especially in some companies like large hospitals and universities. We used mysql (I cannot remember why) and we did a lot of performance testing at that time; we removed all joins which made everything a lot faster. This is no longer the case now but indeed many people still believe it ; not (only) because they saw or tried it back then, but also because it’s less strain on the brain to just do single table selects and use not FKs or joins.

function_seven 3 years ago | |

Back in the day I was forced to ditch FKs in my MySQL application, because I needed a FULLTEXT index on one of my columns, and MySQL only supported that type of index on MyISAM tables (this was on 5.x or something). MyISAM didn't do foreign keys.

It was a pretty central table, and the inability to use FKs there kinda spread outward.

edmundsauto 3 years ago | | |

Did you consider making a 1-1 relationship on a new table that only had the FULLTEXT column? Curious how you evaluated the trade offs

bartread 3 years ago | |

> Another classic is the “joins are slow” argument

Along with the "indexes slow down INSERTs and UPDATEs" argument that you touch on. I mean, it is literally true that indexes make writes slightly slower, and an excessive quantity of indexes (which I have seen) can slow down writes enough to cause problems. But - in general - the slowdown is irrelevant compared with the overhead of querying a table that contains 2 billion rows using, oh, I don't know, a table scan because you don't have even a single index (I have also seen this).

vbezhenar 3 years ago | |

One reason to avoid FK is when your database is partitioned to multiple servers, but that's obvious, I guess, and it's not really RDBMS anymore.

tailspin2019 3 years ago | |

> RDBMSes are highly optimized pieces of software

> Much better than ORMs

These two things are not mutually exclusive though right?

It’s entirely possible to have a lightweight and relatively transparent ORM which makes full use of the underlying RDBMS.

sverhagen 3 years ago | | |

Yeah, I was going to say something similar. But ORMs get blamed for obscuring what's going on, to the point that a developer may end up doing some sort of inefficient 1-to-n lookup that would've indeed been much better off as a SQL JOIN.

I use JPA/Hibernate professionally, as a decision maker, but I don't think I'm in either camp entirely. ORMs aren't a magic wand, but they do help you standardize the boilerplate that you'd end up with one way or the other, in most cases.

Gordonjcp 3 years ago | |

> in fact RDBMSes are highly optimized pieces of software that are especially good at combining sets of data. Much better than ORMs, anyway,

ORMs are just a wrapper around RDBMSes. If your ORM is producing incredibly stupid SQL to query the DB with, you might want to check that you're not modelling your data in a stupid way.

I am by no means an expert, but in general I have found that if the ORM is doing something particularly crazy, it's because my underlying assumptions about the data model is wrong.

axylos 3 years ago | |

> Your coworkers argument about FKs making data migrations difficult is one of them.

Got any arguments to back up this bald assertion?

In particular, I'd love to hear more about how to manage schema migrations on large tables with FK's without incurring lengthy locks or downtime.

Betting the answer is going to involve some variation on "well, don't do that" which is when I'll rest my case.

roguas 3 years ago | | |

There are tools for live migrations for most popular databases. Also a lot of Postgres DDL is very fast and/or capable of happening live.

radicality 3 years ago | |

A lot of it depends on the use case. For example, Facebook - one of the largest (if not the largest) deployments of mysql does not allow any FK constrains. There’s multiple reasons, but one of those is better predictability of db operational perf - a row delete should delete just the row and not potentially trigger N cascading deletes.

pindab0ter 3 years ago | | |

I don't understand “a row delete should delete just the row and not potentially trigger N cascading deletes”. If you want that to not happen, then define that in the database definition. It sounds like you're saying that a core piece of functionality is somehow ‘wrong’, even though that same functionality can be used to make the desired bahviour for this exact use case explicit?

skyde 3 years ago | | |

facebook data model is a Graph where each row store one object “comment” or one association “comment is with post id” between objects .

They made an query and indexing system on top of it to make it fast called TAO.

Without it you need to send a distinct SQL query pet parent object to get list of associated child object which would be awfuly slow.

goto11 3 years ago | | |

Cascading deletes is a separate from FK constraints. You can have FK constraints without cascading deletes.

pharmakom 3 years ago | |

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?

ashkulz 3 years ago | | |

You can use DEFERRABLE INITIALLY DEFERRED constraints so that the check happens when the transaction is committed.

GoblinSlayer 3 years ago | | |

I assume the target is externally generated too, thus can be legitimately absent.

Cthulhu_ 3 years ago | | |

I had this when importing test data; I found it acceptable (since it was just in development) to temporarily turn off FK checking.

thatjoeoverthr 3 years ago |

Your database is is the state of your system. Guard it!

I just ran into severe data corruption at a large client because a programmer four years ago wrote an empty catch block. The system would open a transaction, hit a fault, roll back, then continue writing to the database as if it’s still in the context of the transaction.

I spent some time trying to pin down exactly what it did, and found that many writes went through because of a missing foreign key constraint.

In short: if a particular table of minor importance had a foreign key constraint, there would have been no damage whatsoever, because it would have faulted immediately after the rollback.

You can’t rig up a constraint against every dumb write. But you can rig them up against some of the dumb writes. And sometimes that’s enough.

pif 3 years ago |

I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable.

In other words, the world is full of idiots; and any time I start forgetting about it, I read something like your post and I get a wake-up call.

What does R stand for in RDBMS is you don't use foreign keys and joins?

Please, keep using your FKs, stay safe and don't mingle too much with idiots.

buro9 3 years ago |

I use FKs for most things in an RDBMS... but not for all things.

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.

buro9 3 years ago | |

I should've added...

FK based on the possible size of a table rather than the current size of the table.

FKs are incredibly performance on a near-empty local dev database ;)

vbezhenar 3 years ago | |

> For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted.

    on delete set null

?

Diggsey 3 years ago | | |

That still modifies the audit log, which is presumably supposed to be immutable. It also means you lose information, eg. if this is a user ID, then before you would be able to determine if two audit actions were done by the same (deleted) user. With "set null", you can't determine that anymore.

The parent's point is that the choice should depdend on the situation. Sometimes a foreign key is appropriate, sometimes it is not.

masklinn 3 years ago | |

> For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted.

Or updated. Or prevent the deletion.

But that’s configurable anyway.

scottcodie 3 years ago |

Foreign keys also let the query optimizer make better query plans. This is actually a bigger deal than most people think, the query optimizer can rewrite to semi joins or even eliminate joins completely if the optimizer has better guarantees about referential integrity.

seunosewa 3 years ago | |

Could you give us a simple example?

CraigJPerry 3 years ago |

There’s a pithy quote by someone famous in DB circles who said something like “normalise until it hurts, de-normalise until it’s fast enough” - I’m vague on the exact words used but that’s the gist of it.

I’ve never come across a scenario yet where this wasn’t sound advice.

I tend to lean heavily on my DB as well. E.g. I tend to push all state down to the DB and out of the application. I work in environments where it’s common for developers to want to disable FK constraints, and i temporarily do sometimes during specific bulk operations during releases for example. The usual reasons others will suggest relaxing FK constraints permanently will be due to the need for audit logs or soft deletes but i have patterns for these too.

Foreign key constraints are pretty awesome, all databases I’ve worked in so far have escape hatches for when they hurt too much.

evdubs 3 years ago | |

I've heard it as, "Normalize until it hurts. Denormalize until it works."

Still, in my experience, database normalization has seemed like less of a performance impediment than queries written without considering an execution plan and indexes.

josephcsible 3 years ago |

Giving up foreign key constraints because they cause errors is basically the same mistake that the monk in http://thecodelesscode.com/case/115 made.

metadat 3 years ago | |

Yes, this 100%. Though, as other comments have printed out: you need to take application and scale into account.

Fundamentally it's a question of how one should go about handling multi-tenant situations.

Rexxar 3 years ago | |

I didn't remember this little stories where sometimes so cruel.

quickthrower2 3 years ago |

Foreign keys can be removed during migrations and added back after. You would also disable triggers and check constraints too.

I like foreign keys, check constraints and tight data types. Might as well constrain it and limit the scope for errors.

Application programmers can write some buggy code. The DB should provide a line of defence.

If you don’t have foreign keys that should be a design choice with a legitimate reason the entity can become orphaned.

You need a definition of what that means in real life. E.g PERSONID id 1012 and there is no associated record. This kinda means you need to look at another entity to know what the first entity “means”.

This might be useful for data that needs to sit in distributed databases.

Tade0 3 years ago |

To me "let's wholesale throw away this non-deprecated language feature" is a major red flag.

I've found that proponents of this usually either don't fully understand the feature in question or made some major mistake in the implementation, which in turn causes problems that manifest themselves when they try to use said feature.

forinti 3 years ago |

You need FKs to ensure you don't delete data that's still needed. If your data is important, you have to use FKs.

You also need to index your FKs so that the database does not have to do a full table search before you can delete a row. This is often overlooked.

Your friend can do his data migrations without FKs and create them afterwards. This is quite a common procedure.

ivraatiems 3 years ago |

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 when it goes in so it never breaks the application layer." That school would say foreign key constraints are critical.

Another school of thought says "I want all the data in my DB to be retrieved and inserted quickly. I want the application layer to do any error-checking that is necessary, or, I want to be in a situation where I can always fail gracefully if there's errors in data validation."

Still another school of thought says "I don't trust those programmers to write good application code, so I will insist on normalized data for that reason," and yet another says, "I don't have control over the DB schema, that's some DBA's job, so I will just do all my validation in-app."

The point of this being, there's tradeoffs either way you go.

Personally, I typically would rather have the application layer do the validation and even the joining of data, a lot of the time, when the application is high-volume. 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. The DB is also a pet with many owners, whereas the infra for my applications is owned by my team. So, it's better for me to do relationship validation in code myself. (We also do not use a heavy ORM, again for performance reasons. Just Dapper.)

At my previous job, the situation was the opposite - we weren't under such load at most times that it mattered if the queries were performant, we had Entity Framework building relationship, and EF will blow up if you ask it to build relationships where none exist. So, we needed more normalized data, and that was what we went for. But even then, not in every situation.

jrumbut 3 years ago |

Foreign key constraints are easy to remove if they become a problem but almost impossible to add once data integrity problems arise (and I've never seen them not arise in projects without FK constraints).

The small number of people with high enough scale that they can't use them know who they are, the rest of us need to think carefully when performing database migrations and reason out the order of operations required to maintain data integrity (sounds like a good idea anyway?).

dspillett 3 years ago | |

> sounds like a good idea anyway?

Heck, yes. If you are butting against constraints then you may have misunderstood the problem.

I don't mind people turning constrains off for a mass migration under the following conditions:

• This is not production, or if it is production you are in a maintenance window during which you have exclusive access to this DB

• The data you are piling in will be verified against the constraints once the job is complete (no turning things back on with “WITH NOCHECK”), and you have a rollback plan for if that fails (“restore from backup take before the maintenance window” might be acceptable, if that can be done in the timeframe of the maintenance window and you are happy taking questions from your team/clients/management if this means planned updates have to be completely postponed).

• You can explain why not getting the data to be modified in an order that allows the constraints to be kept on through the process would be significantly more complicated (simplifying migration code is a valid reason for temporarily turning off constraints if it makes maintaining the relevant code less error prone) or significantly slower (sometimes doing it most right unavoidably takes more time) or both.

jrumbut 3 years ago | | |

RDBMSes don't do a lot of extra stuff, if they throw an error it's usually for a reason.

y1426i 3 years ago |

There are many reasons to not use foreign keys, but it also depends on the kind of application. For small databases, foreign keys do make things simpler from a validation standpoint.

When building systems for scale where the databases may grow large, foreign keys can cause many issues -

- ORM features around foreign keys can easily bring your system down when joining large tables with incorrect/missing indexes during heavy loads

- As the table grows, not having foreign keys makes it simple in taking out large tables into big-data solutions in the future

- The schemas and relations are sometimes hard to understand during the initial phases of application development. Not having those relations makes changing schemas simpler and faster.

- Sharding tables is much simpler when there are no foreign keys

- It helps to add some of the reference logic in the application rather than the database. Databases are the bottlenecks when it comes to IOPS and scaling. The more processing you move to your application server, the better scalability you can achieve.

throwawayffffas 3 years ago |

Yes and no, there are cases that you should not use FKs mostly for things like audit logs, order logs, stuff you never want deleted or modified in general.

In general it depends on whether you intend to use the database to drive a system, or whether you intend to use the data for reporting and data analysis.

Inconvenience in migrations is not a legit reason, if your migration would fail if you had FKs but doesn't because you don't you just broke your data, the errors are there to protect you. In other words your colleague is straight up wrong.

bazoom42 3 years ago |

Your colleagues logic is akin to turning off the smoke detector because it starts beeping any time you barbecue indoors.

vbezhenar 3 years ago |

Of course I do. I think that it's crazy not to use database features to increase database integrity. I use foreign keys, I use not nulls, I use checks, I use triggers sometimes.

All my issues with databases usually stem from the fact that someone did not use enough checks and we got dirty data nobody knows what to do with.

My only non-conventional usage of database schemas is text field lengths. I have rule: it's either 20, 200 or 2000. It should be big enough to fit any non-insane value. Like 200 should be enough to fit a phone number. The main point is not to let absurdly broken data in.

devmor 3 years ago |

I use FKs because I have built my career on refactoring old software.

I have seen over and over firsthand the kinds of data integrity problems that come from leaving the decision to the business software and those who meddle asynchronously with data.

You can always rewrite software. Rewriting bad data is not only difficult but often impossible.

steve_gh 3 years ago | |

This:

"You can always rewrite software. Rewriting bad data is not only difficult but often impossible."

charcircuit 3 years ago | |

>You can always rewrite software. Rewriting bad data is not only difficult but often impossible.

Isn't that an argument against FKs? It's easier to rewrite the software to handle FKs than to deal with trying to setup FKs with bad data since it's difficult to fix.

dmak 3 years ago | | |

Data often outlives the teams that input it. Often the case is, you can't recapture that domain knowledge to rebuild the missing data.

CRConrad 3 years ago | | |

> >You can always rewrite software. Rewriting bad data is not only difficult but often impossible.

> Isn't that an argument against FKs?

No, it's an argument for FK checks.

> It's easier to rewrite the software to handle FKs

Except once you have that bad data, you don't know what to do to fix it in whatever language your app is coded in any more than you do than in SQL. Once you know that, you can just as well do it in SQL as in any other language. Or, if it's just that you know that language better than the SQL language, by all means write a separate one-time fix-the-data app in that language, run it once to fix the data...

And then enable the FK checks so you won't have to do it again.

> than to deal with trying to setup FKs with bad data since it's difficult to fix.

Which is why you want FK checks in your DB from the beginning.

[Edit: Fix bad original editing.]

mattewong 3 years ago |

There is only upside to using foreign keys. When enabled, the benefits are obvious, so I won't discuss, and as for the drawbacks, the only drawback is performance. But if that's a concern, then guess what, on most databases, you can disable foreign keys.

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

bvrmn 3 years ago | |

I have an anecdote from my experience. We had an unpleasant debug week in search of an cause of slow insertion query in PG to a particular table. 15 FKs to the table were the reason of a bottleneck.

silisili 3 years ago |

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.

beachy 3 years ago | |

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 years old, and it is as fresh as the day it was created. If it has FK constraints, it's even healthier.

That means that the more value is embedded in the database - FK and of course many more constraints - that value will live for decades.

llanowarelves 3 years ago | | |

I have lived the rise and fall of all those things in my own projects (rewrites, you name it) using other's ORMs (for example). Handling as much as you reasonably can at the data layer is definitely the way to go.

Commenting just to draw attention to your comment.

iforgotpassword 3 years ago |

I use FKs and have migrated/updated the scheme multiple times over the years. If something goes wrong during migration that means I don't understand my own scheme properly. Getting rid of FKs for that reason feels like disabling all compiler warnings, or wrapping every single method in java in a try-catch block with an empty catch part.

dbsmith83 3 years ago |

I have not worked on a system where the performance mattered so much to consider not having a FK. I personally think that the key relationships should reflect the nature of the data. It prevents a lot of errors so that new devs don't create data which should not be able to exist. Don't make invalid data have a chance to exist or be representable. Make it difficult to pollute the db because relying on your app layer is risky

alberth 3 years ago |

FK’s is what makes a relational database “relational”.

It sounds like your colleagues want NoSQL (key-value) given their no JOINs policy.

zergu 3 years ago | |

"Relational" comes from https://en.wikipedia.org/wiki/Relational_model Table = relation

IshKebab 3 years ago | | |

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.

oxff 3 years ago |

How is this even a question, besides denormalization cases, FKs are essential for data integrity and management if you have even slightly relational data (data that is composed of joining tables), it guarantees you a source of truth..

golondon 3 years ago |

No, especially on large tables with billions of records. They make online schema changes impossible. More details: https://github.com/github/gh-ost/issues/331#issuecomment-266...

glasshug 3 years ago | |

Curious why no one else had mentioned this. Are they using online schema transforms besides gh-ost and Percona? Does Postgres handle this better than MySQL?

golondon 3 years ago | | |

Postgres has index concurrently which is better or few other instant things which MySQL 8 also has but you still need to be careful.

But some things like adding a column with default value is still hard.

https://www.braintreepayments.com/blog/safe-operations-for-h... this article has a good guideline on this.

belltaco 3 years ago |

Why not just turn off foreign key checks during migrations and after validating the data, turn them back on? SQL Server lets you do this.

pmontra 3 years ago | |

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.

masklinn 3 years ago | | |

> But you could get duplicate key errors when turning fk on.

Why? Fks don’t require unicity. Or do you mean that you target non-unique columns? That sounds… horrendous.

But regardless sounds like something you’d want to resolve before declaring the migration finished.

pards 3 years ago |

> difficulties during data migrations

It's not difficult; it merely requires that you think about the order of the migration operations.

> He'd rather have a smooth data migration process than having an unexpected error

This should be grounds for dismissal. Data integrity problems should be dealt with immediately instead of making it someone else's problem later. He's just kicking that "unexpected error" down the road to some poor soul that will spend weeks trying to figure out how the data got so messed up.

If you don't care about data integrity then use a different data storage solution.

bartread 3 years ago |

My other beef with no foreign keys is that it makes a database a lot harder to understand.

There are any number of tools that will generate me a pretty and useful database schema diagram if I point them at a relational database. This is incredibly handy when you're new to a database and need to figure out which tables to query and update, and which (gasp) sprocs you need to call. I've been on projects where people have been poking around in the dark, and then a good database diagram has saved us days or weeks of effort trying to figure out how to make something work. As I say, there are plenty of tools capable of generating one of these in seconds or minutes[0].

However, if you don't have foreign keys, the utility of such a tool is severely diminished because you just get a big pile of nodes representing tables clustered at the top or bottom of your diagram (depending on exactly which layout algorithm is being used and how it's been configured).

[0] Many years ago I and three colleagues built one of these: Redgate's SQL Dependency Tracker (https://www.red-gate.com/products/sql-development/sql-depend...). It was pretty neat because you could build a diagram spanning databases, or even linked servers, and unlike most other tools at the time it could handle thousands and thousands of database objects, but the product name doesn't really help get across that it's fundamentally a diagramming tool. I built the dependency engine, the graph, and radar views. We used yFiles from yWorks for the graph layout calculations, with a bit of extra hackery, but I seem to remember yFiles lost compatibility with a newer version of .NET at some point so (or something along those lines) so RG ended up swapping it out for something else.

saint_abroad 3 years ago | |

> There are any number of tools that will generate me a pretty and useful database schema diagram if I point them at a relational database.

I'd even go so far as to design database schema with these ER diagram tools in mind: if the automatic diagram is messy then that's more-often-than-not a code-smell in need of refactoring (before being hit with production data).

sebazzz 3 years ago |

Besides the other reasons mentioned, FK's are also implicit documentation - even if you disable them.

oedo 3 years ago |

Most (non-gigascale) applications that are non-trivial enough to use a relational database in the first place will benefit from using foreign key constraints (ie delegating enforcement of foreign key relationships to the database). Though, context can matter: system architecture, RDBMS limitations, etc. (eg IIRC, FK constraints complicate certain kinds of migrations in MySQL at scale).

>He rather have a smooth data migration process than having an unexpected error and abort the whole operation

That's not a good reason. It doesn't sound like he's making an educated decision based on context-- it sounds like he's sacrificing data consistency to make his own work easier. If that's the case, perhaps he should do away with all integrity constraints (primary keys, checks, et al). Then, he could enjoy a "smooth process" for adding new data, too. No more pesky errors-- just blissful, silent corruption.

>To be fair, as far as I know, he never had major data problems.

Yet, anyway. He has intentionally limited his visibility over at least one kind of problem (invalid references). His problems might not be clear until they start causing noticeable issues elsewhere.

>He is not the only one I've met who insisted on not having FK. I've even seen large systems prohibit using JOIN statements.

Allergy to the relational model. Many such cases!

gigatexal 3 years ago |

In 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.

cratermoon 3 years ago |

Foreign keys are an implementation detail specific to the relational model. In the stereotypical example, an Order might have several Line Items, so each Line Item has a FK back to the Order, via the FK OrderID. (Ignore for a moment Assembly/Part where each Part can be in multiple Assemblies). Anyway the point is the that an Order has 1..N Line Items (we can assume an Order with 0 line items is degenerate and not allowed). What matters is that we can know the Line Items that belong-to an Order and the Order to which a Line Item belongs.

In a strict relational model implementation, the only way to reify that is by the FK of the Order in the Line Item, but in some other implementation, say a generic programming language, the Line Items might be an array or similar data structure that is part of the the Order, and the programming language implementation keeps track of the pointers or address offsets or whatever detail it cares about.

Anyway that's all beside the point.

In my experience, if the IDs are not autogenerated by the relational system, then it's relatively easy to migrate, however when data is full of IDs defined via some AUTO_INCREMENT behavior then migrations become an awful mess and any system (and this happens more frequently than you might expect) where a specific ID starts to have semantic meaning (oh, the customer's ID is 387437. whoops) then all bets are off and you might as well just give up and accept that your auto-generated IDs are now fixed for all time and can't be changed.

Oh and just to add, I have notebooks from previous employers where I have written THE Important IDs to Know, which started life as auto-generated numbers but which now are enshrined and encased in acrylic to the extent that years later they are important tribal knowledge.

Rudism 3 years ago |

Saying you refuse to use FKs because you keep running into problems with data migrations is like saying you refuse to use arrays in your code because you keep running into problems with out-of-bounds index errors.

pram 3 years ago |

Yes 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.

QuiCasseRien 3 years ago |

"use FK, you fools!"

FK maintains your database integrity. If you care about data, just use it !

__d 3 years ago |

I've worked on a couple of systems that use foreign keys (and other constraints) specifically after a migration to ensure all the migrated data is consistent, but then drop them again to reduce the performance overhead of having them checked during production use.

samatman 3 years ago |

Do... you not turn the foreign key constraints off during migration?

In SQLite it's a pragma, in Postgres you turn triggers off, those are the ones I've personally done but surely any relational database has this ability for this specific reason?

wahnfrieden 3 years ago | |

best to use tools for this such as https://github.com/fabianlindfors/reshape or gh-ost for mysql

sverhagen 3 years ago |

Absolutely "yes" on foreign key constraints.

There _is_ a scale, though. I have worked in enterprises where the database is the "contract" and where the business logic is implemented in the database, with triggers and all sorts of constraints and what not, in addition to foreign key constraints. But I now mostly work in service- or micro-service-oriented places where the "contract" is a REST(-ish) API, business logic is implemented in Java, and the database is exclusively owned by that one code-base, where not all business logic is replicated from the Java code into the database (still foreign keys, though!)

astura 3 years ago |

Your colleague doesn't understand relational databases, which, unfortunately, is fairly common - classes on RDBMSes are electives in most CS programs. It's scary/concerning this person is being allowed to operate like this - I'd suggest your company add design reviews and code reviews to reel this sort of rouge behavior in.

...Of course, if nobody understands relational databases at the company, then that won't help...

Even if data migration was difficult with foreign keys for some reason, they can be disabled at the time of migration - no need to put weird constraints on day-to-day operations.

viraptor 3 years ago |

I've joked before that I can often see which tables were created first and which ones were added over a longer time by checking where did people start giving up on foreign keys. But it's kinda true too.

I see them the same as stored procedures. If you use them, you better use them everywhere and have all your data consistency model in the fk and stored procedures. Also, figure out how to disable them during the more interesting online schema migrations. If you can't commit to that, they'll only cause issues down the line.

mejutoco 3 years ago |

I can relate partly to the problem. Sometimes you want to import a single table from a backup and, because of FK constraints, a very specific order in the tables is required, and can span many tables depending on the complexity of the data.

As an alternative you can use Foreign keys without constraints. This way you get the conveniences of them without the migration problems. You can do this permanently (a bit wilder) or just before an import, and re-enable it later.

Canada 3 years ago |

I go for constraints every time. Data Integrity is king as long as you can reasonably afford it.

Your data migration aborted on an unexpected error? Well in my view that's a problem and you need to figure out why that is happening and fix it.

But hey, you can always drop the constraint when you decide it's not worth it, and you can always add it back again after. Depends on the application too, sometimes it just doesn't matter if you make a mess.

BWStearns 3 years ago |

It can be annoying doing migrations where it turns out your assumptions about FKs were wrong. However, it's absolutely worse to deal with a system where relationships aren't enforced at the schema level. You still end up validating the existence of the related rows, but it happens all over your codebase in an ad hoc manner and you'll still end up with bizarre bugs.

PaulHoule 3 years ago |

It is an old debate. You can get into awkward situations when you save and restore tables, everything has to happen in the right order and there's always the fear of some circular situation.

I'm remembering the time I was working at a place that had a huge number of Microsoft Access, Microsoft SQL Server and mysql databases and I was the first person they'd hired who knew how to do joins and they thought it was pretty scary.

sergiosgc 3 years ago | |

That's not how you migrate. You disable the foreign keys, load the data, then enable them (which checks everything is ok).

exabrial 3 years ago |

> His main argument is difficulties during data migrations which he frequently encounters

He's just kicking bugs down the road. That's not engineering.

hyperman1 3 years ago |

For me it's a casevof pick your poison.

If you have no FK, the day will come that your data corrupts. Tiny application bug, wrong manual data fix, incomplete datamodel communication. It's a case of when, not if. And when it happens, it can fester for weeks or months, corrupting all kinds of data in unfixable ways.

If you have FK, you can go all-in on relational. The stuff is so powerfull, you'd be dumb not to. But then comes the too-smart-optimizer problem. Just like with compilers and opengl shaders, the tiniest change might make you fall off the optimizer's preferred path and get a way-to-slow version. Even when no code has changed, a minor DB version or even a tiny shift in statistics will kill you.

Personally, I tend to consider integrity more important than performance, but both have a zone where they are good enough to make trading off worthwile.

So I do FKs and joins, and count on rigorous testing and monitoring to keep things in check. Someone else might decide otherwise. Both strategies require you to do the unsexy part of the wirk, or a harsh punishment will follow.

dedomena 3 years ago |

Well its an interesting subject, yes it is RDBMS i.e. relational database, tables=relations, alas it does not automatically mean that just because you have a relational database you must use FK. Most of relational databases theories were created at the time when databases were few megabytes in size so the theory start quickly collapsing with terabyte size databases and larger. I would say it is truly depends on your strategy - I've seen companies remove all FK and simplify databases management and vise versa. First and foremost I would test just how many FK violations are really there - if you have FK violations into thousands per 24 hrs you might need to take another look at your app on the other hand if you have very few FK violations does it make sense to lace your database with FK's thus significantly complicating administration? All and all the databases are designed to store your data and that is it, yes of course you can use the database to QA bad coding practices but it does not make it scale very well.

wingshayz 3 years ago |

It would seem a bit crazy to me to ditch FKs for that reason. Why not just drop the constraint? I would much rather keep my data integrity.

The issue with managing the relationship just in code is if you ship a bug to break the relationship, you now have to manually fix your data, and if you want to find out when or where the bug was introduced, you're looking at commit history instead of a migration history. Same thing when it comes to making manual updates or adds in the db. Even if it's just on a dev stage, if your code makes an assumption about the constraint which isn't true, you can end up with bugs or exceptions on dev, which is also annoying. If you want to remove the assumption of the relationship from the code entirely, that would be more understandable, but not if instead it means replacing what would be an efficient constraint and join with a separate query.

dkjaudyeqooe 3 years ago | |

That's how I've always done migrations. Load the data, then create the FK constraints and indexes. It's not hard at all but slightly more work.

Cody_C 3 years ago |

I prefer relational databases for most cases unless I really need to use a non-relational store. I also use foreign keys whenever possible. From what I have seen, anything to enforce referential integrity is a benefit in most cases. It is more comforting to have a delete fail than have many other queries mysteriously fail down the line.

In most cases, a well designed database covers a multitude of sins and makes life for future you and your future team much easier. Some of the biggest dumpster fires of code I have seen started as a smoldering dumpster from terrible db choices that were made far harder to fix when it had been in production for a few years.

If someone put a gun to a developer's head and threatened to pull the trigger if the database was crap, sadly they would be forced to pull the trigger most of the time.

zzzeek 3 years ago |

> He rather have a smooth data migration process than having an unexpected error and abort the whole operation that the migration is only a small part of.

that's some take, because if that "unexpected error" is not reported because FK integrity was turned off, that means the data containing integrity errors goes right in. Now your database is corrupt. Dealing with a DB where random rows here and there are not conforming to implied-only constraints is zero fun. in my own experience, things like the main page of the production site is a 500 error, with a stack trace deep into some logic nobody has looked at in two years, run the identical code on staging, works fine. Fun stuff! Seems like an odd choice to let errors like that stream right into your production DB without checking.

astura 3 years ago | |

This is one of the many problems with "senior" engineers who have been in the industry four years over three different jobs - they don't stay long enough to see the consequences of their shitty code.

darepublic 3 years ago |

Yes I do! Your colleague reminds me of a colleague I had who would copy and paste code into node_modules via a shell script to share it because they didn't like to learn about npm publish. Do things the right way if you can and your life will be easier, even during the data migrations.

JohnDeHope 3 years ago |

I do app databases for a living, so I thought I'd dump my 2 cents here. I am very much not on a high horse about it. I'd be perfectly happy to use FK if somebody felt strongly about it. I myself have never found them helpful. My databases are small enough and I write all the SQL access code by hand, so nobody is touching the data except me, and the SQL that I write myself. Things being half-deleted isn't a problem. In fact I can't ever remember where that has happened. I have lots of other problems, but that's not one of them. PKs, indexes, views, etc. I use all of that stuff, but not so much FK. YMMV. Prohibiting joins is throwing the baby out with the bathwater.

sethammons 3 years ago |

By default, yes, FKs and joins. However, I tend to work on high scale systems generally backed (primarily) by mysql. To scale out (tbs of data, hundreds of tables, millions of users, billions of daily interactions), we've had to remove some FKs, avoid db triggers, use caching heavily (including piggy backing requests where you don't serve an identical request from the db, you wait for the response and returned the memoized version). When you do choose to remove a FK, the need doesn't go away, you just move it. Now you have to look into data sync and out-of-band integrity checks and you have to have a plan on what to do when you go looking for data that is no longer there.

throwaway22032 3 years ago |

The entire point of using an RDBMS is to have constraints IMO. Foreign keys are one of the most basic forms of constraint.

"It's never gone wrong yet" is a good way to get yourself electrocuted when you're changing a light bulb.

thefz 3 years ago |

Yes, when I did develop on MSSQL, I used them all the time. They force you to think, and hard, and saved my bottom quite a number of times. I then had to maintain for the last years a system with surrogates only and I hated it.

nablaone 3 years ago |

If you have any business app (OLTP) using FK is no brainer. I know devs whom fights for 99% code coverage and do not use any constraints in the database. Fixing bug in the code is easy. Fixing data inconsistency is not.

ajorrell 3 years ago |

Hello frogcoder, Yes, I use FK's for non-trivial systems. Personally, I believe in the enforcement of FK's via DDL as they document the schema and serve their intended purpose of maintaining data integrity according to the rules of the schema. Enforcing constraints and any other system constraint is part of system design. From a debugging perspective, I would rather have the RDBMS return an integrity error when the layers using it don't follow the rules, instead of chasing down where the contraint was not enforced in every layer.

eric4smith 3 years ago |

Yes.

Mainly to cascade delete though, since we were robust enough to insert proper data.

This was spurred on in new projects because in a very very large older project with many many tables we had problems with orphaned records.

(PostgreSQL)

kureikain 3 years ago |

I used to scare of it thinking why do I need it? everytime I delete sth it annoying me. I read blog post of GIthub not using FK key, and try to convince myself that FK isn't worth it.

One day, I decided let try this out. And turning out it wasy easiser than I think. When delete data just make sure to use cascade or ensure its associated data is deleted, which make sense.

Once I embrace it, FK now becomes my friend to enter data consistency, I cannot imagine how did I live without it before.

mccorrinall 3 years ago | |

It’s can become pretty annoying if you want to alter your database schema.

astura 3 years ago | | |

How?

floppydisc 3 years ago |

Yes, but it definitely depends on the model I'm putting into the database.

Specifically I hadn't thought through an Order model and the OrderLines ended up being dependent directly on the product meaning through an FK. The orders wouldn't "settle" once they had been completed, since the product could be updated and change the values of the orderline and order. Dumb dumb. It was one of the cases where denormalizing data, very much, makes sense

Philip-J-Fry 3 years ago |

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!

connormcd 3 years ago | |

If you've got a bit of time to spare, here's a 30min walk through of physical design in the database. https://youtu.be/x0P4zAptTiA

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

bawolff 3 years ago | | |

I mean, was that 20,000 transactions that are likely to have lock contention related to foreign key constraints? Because otherwise you are measuring the wrong thing.

alphabettsy 3 years ago |

I can’t imagine not using FKs. Data integrity is more important than smooth migrations and if that’s the issue then he needs to learn more.

wahnfrieden 3 years ago |

I'm removing them because I'm moving to a fully async realtime / offline-first architecture and pushing conflict resolution to the clients or to async server processes where automatic resolution is possible. I don't want to show errors to users during async background sync processes for data they may have populated days or weeks ago.

hk1337 3 years ago |

I'm working on a database currently that has "foreign keys", they are the relational fields but there's no foreign key relationship setup in the RDBMS. Also, there's multiple relations on one table instead of just 1-2, so it's a bit like everyone has their hands in the cookie jar. It's a pain in the ass.

drderidder 3 years ago |

Of course. Foreign keys are elementary. Try recursive CTEs. You can get ridiculous performance gains by not having to marshal data into the application space. It’s all about using the right tool for the job. If you’ve done solid system-level design then running migrations probably shouldn’t be the primary driver of your schema.

AdrianB1 3 years ago |

At work, we use foreign key constraints when it makes sense, that is over 50% of all cases considered. The factors considered are risk and performance: what is the risk of bad data to get in the tables and what is the impact of that happens vs the cost of extra processing time or extra hardware to compensate.

CRConrad 3 years ago |

> He is not the only one I've met who insisted on not having FK.

A) The world is full of idiots.

> I've even seen large systems prohibit using JOIN statements.

B 1) WTF is wrong with these people? What do they think an RDBMS is for?!?

B 2) See above, exhibit A.

Topgamer7 3 years ago |

Is there a way to have foreign keys without the index and integrity constraint. I would love to be able to keep the relational mapping at the db layer. (I almost always actually want the integrity and indedx, but I'm just curious if its possible)

tuatoru 3 years ago | |

Yes, just have columns for all the primary key fields of the referred-to table in the dependent table.

Then it's up to all your apps to guarantee that they fill those fields in correctly, and do the reference check when inserting or updating rows in the dependent table.

You also need to write checking programs that report foreign key errors on a timetable that suits (every minute, every hour, every day, whattever).

Report the errors to someone who understands the significance of them, and can fix them.

tommica 3 years ago | |

Isn't that just storing the value in a normal column?

Topgamer7 3 years ago | | |

What I'm specifically looking for is a foreign key relation so that you could for example still generate a chart of relations. For example an audit log you wouldn't want the consistency check, but it would still be nice to know it links to X tables.

jokethrowaway 3 years ago |

I use foreign keys with postgres, I used not to in mysql ~10 years ago. I feel it depends on the level of support of your db / how painful it is to override things if needed.

Performance can be another reason for skipping them but modern dbs are pretty good.

Simon_O_Rourke 3 years ago |

I'd love to have prohibition of using just any old key in a join, but Snowflake, and Redshift to a lesser extent, just don't care.

In fact, Snowflake really really doesn't care to the extent that I've seen non unique primary keys in tables.

mamcx 3 years ago |

> His main argument is difficulties during data migrations which he frequently encounters. He rather have a smooth data migration process than having an unexpected error and abort the whole operation that the migration is only a small part of.

NUTS!

> I suspect the errors might be mainly caused by not considering data integrity at all at the first place

Spot on.

----

I deal with interface with MANY ERP-like software with as many "database designs" as you can't imagine, and have face the fun of interface with many system in my long career.

Among DOZENS only 2 major packages have zero issues and are the only ones with competent schema designs.

Let me tell you how bad is it:

One of my interfaces is with a package that, somewhat, manage to mix all the ways to be wrong about RDBMS:

- MySql, not a good start

- Only Strings and Ints datatypes, and the Ints datatypes are not many

- This means ALL data is suspected (dates, money, and yes, strings too)

- The tables are named `UNCC_00001, UNCC_00002...`

- The fields are named `FT_0001, FT_0002...` and obviously the first is the PK. This pk is purely advisory, other fields can be part of the PK but you can't know looking at the schema

- NO FK at all, so ALL TABLES have integrity issues.

- NO indexes, because why make joins or lookups faster?

- Weird normalization, like yes, no, maybe?

- And no option to let me add some sanity to the DB, because the database is (in my niches) "property" of the software makers so can't be touched, only queried.

And not tell the rest, because this is only the query side, the CRUD side is nuts, and not wanna remember much about it.

---

RDBMS is one of the most simplest ways, to get FOR FREE, and minimal effort, a sane, well behaved and performant system.

Refuse to use them is NUTS. Refuse to use the abilities they have is NUTS.

I only have ONE actual company (in +20 years) with a real reason to go NoSQL, and that only was for a fraction of the thing (in fact, the data was mirrored to a RDBMS for reporting!). And maybe that was true 5-10 years ago, today I think modern SQL engines catchup more and more on the scenarios (Today I have used timescale just for speed up log processing, and was so much nicer that deal with weird log storage engines).

ryanisnan 3 years ago |

Fear of having integrity issues raised is NOT the reason not to use Foreign Keys. Performance at scale is. Foreign key constraint checks can be costly on write intensive applications with many tens of thousands of QPS.

jenscow 3 years ago |

> He rather have a smooth data migration process than having an unexpected error and abort

Personally, I would prefer errors to become immediately obvious, while I'm active and there's a roll-back ready to be applied.

lisperforlife 3 years ago |

I tend to use foreign keys everywhere. The only time that I would skip it is when I do not need to cascade deletes. These are mostly metadata tables that will be archived on a periodical basis.

ddba 3 years ago |

Hard to believe so few talks why fkeys are a big no no in mysql. Go figure out how to use any online schema modification tool short of a blue green deployment or God forbid, triggers.

amtamt 3 years ago |

from https://thedailywtf.com/articles/directive-595

Dear Database Architect,

Directive 595 Part 2 is as follows.

  "Foreign and Primary Key constraints give lack of flexibility, more 
   costly evolution, inhibit the use of the database acting as a 
   service to applications and make it an inhibitor to evolution."

As such, please remove from all production databases.

Sincerely, Chief Architect Gerald

habibur 3 years ago |

I don't use foreign key constraints on the database.

I create all fields as NOT NULL and use empty string in place of NULL.

Last time I tried foreign key constraints can't work in an environment like this.

sgjohnson 3 years ago | |

> I create all fields as NOT NULL and use empty string in place of NULL.

...but why?

habibur 3 years ago | | |

Don't have a use case for a field being NULL instead of "".

Say, if I want to check how many records I don't have value for "ref", I don't want the count(*) query to show

    count(*) ref
    12000 (null)
    17030 ""

I want both added together. That's for example one simple reason out of many others.

brightball 3 years ago |

Yes. You can always disable enforcement when you need to run a data migration. It’s no reason to avoid having them.

Referential integrity saves hours of pain from weird DB issues down the line.

chasil 3 years ago |

I use FKs in databases that I have designed.

I also interact with VAX applications that use Rdb (now owned by Oracle) that avoid constraints of all types for performance reasons.

hcayless 3 years ago |

Christ Jesus, yes.

I mean, there can be reasons why not, but why use an RDBMS at all if you’re not going to take advantage of its features?

m-p-3 3 years ago |

Looks like your colleague wants to ultimately manage a NoSQL server, and doesn't fully understands RDBMS'es.

forgingahead 3 years ago |

Yes - is the alternative even a thing? I feel like I'm in Wonderland suddenly.

skyde 3 years ago |

prohibit using JOIN statements is really cargo cult :-)

Join is simply an index scan. And is guaranteed to be cheaper then the ORM having to send many request to the server.

pornel 3 years ago |

Nah.

Everyone here seems to be super diligent about FKs. I wonder if that's a sampling bias.

I've worked on a few projects where they were considered an unnecessary hassle, especially when the RDMS had some performance or functionality limitations when using them. Also "on delete cascade" seems scary. Plenty of systems even just set deleted=true instead of actually deleting (at least before GDPR).

Not every CRUD app treats data integrity as a holy grail. A social network for hamsters can lose a comment, no big deal. Some applications threat databases as a bag of key-value pairs, and the inconvenience of migrations ends up with a JSON in an "everything_else" column. Move fast and break relational integrity.

stormdennis 3 years ago |

I always felt slightly ashamed for the fact that I don't use foreign keys. I feel better having read this thread!

CRConrad 3 years ago | |

> I always felt slightly ashamed for the fact that I don't use foreign keys.

Good, that's the correct reaction.

> I feel better having read this thread!

Don't.

KronisLV 3 years ago |

I think that this would make a good HN poll, for example: https://news.ycombinator.com/item?id=21231804

Though the answers would probably vary and there's most likely lots of nuance per individual case (which might matter more than just yes/no), personally I can think of the following as examples:

  - yes, we use foreign keys
  - yes, but we use them in testing environments and turn them off in prod
  - no, we don't use them because our database doesn't support them (e.g. distributed like TiDB)
  - no, we don't use them and check integrity and orphaned data ourselves
  - no, because our system design doesn't allow us to use them meaningfully (e.g. OTLT and EAV)
  (also, talking about whether to cascade or not might be useful, e.g. whether you want to manually clean up related data, or not)

Someone else mentioned varying schools of thought, which rings true. Personally, my opinions about database design in general are along the lines of:

  - avoid EAV and OTLT outside of very specific cases, have multiple tables over few (e.g. employees, employee_contact_information, employee_vacations, employee_notes instead of employee_fields and/or employee_field_values)
  - have foreign key constraints across your tables, so that you might not end up with orphaned data, *consider* cascading the constraints (depends on requirements)
  - use views liberally, especially for complex requirements in regards to selecting data, so that your app (or ORM in it) can map against it in a simple manner
  - outside of batch operations, prefer to modify data through the app, instead of procedural SQL, since that's easier to debug; I'm yet to see someone use breakpoints/watches for stored procedures successfully

Though my ideal database design probably looks way different and scales slightly differently (which hasn't mattered as much yet) than someone else's.

There are people who want to build their entire database around a "classifier" system, about which I wrote previously here: https://news.ycombinator.com/item?id=32416093 (this also makes the DB hard to visualize as ER diagram because of meaningless links, and sometimes makes the DB hard to use without the app, e.g. type_enum_value vs table_name).

There are people who want to do everything in procedural SQL (I've seen application views call stored procedures to fetch all data and validate forms), there are those who don't want to touch it with a 10 foot pole.

It really varies a lot, though in my experience it's invaluable to be able to feed a database into something like DbVisualizer and get an overview about how the different tables are related to one another, basically like documentation: https://www.dbvis.com/

bottled_poe 3 years ago |

Yes, until performance prohibits. But that’s usually in the billions of records or more.

SnowHill9902 3 years ago |

Are you trolling?

hestefisk 3 years ago |

Yes, always.

hknmtt 3 years ago |

Never did. Never saw any need for it.