46 ms·

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

• exabrial 4y 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 4y 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.
• mamcx 4y 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).
• ddba 4y 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.
• ajorrell 4y 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.
• BWStearns 4y 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.
• oedo 4y 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!
• CRConrad 4y 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.
• belltaco 4y 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 4y 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.
    • deleted 4y ago
      [deleted]
    • masklinn 4y 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.
      • pmontra 4y ago
        You're right. I meant the troubles we get if we turn off all constraints to ease the migration, end up with duplicate keys in the target table because of mistakes, turn on constraints, errors.
        • CRConrad 4y ago
          > ...turn off all constraints to ease the migration, end up with duplicate keys in the target table because of mistakes, turn on constraints, errors. [Emphasis added -- CRC.] But that's a good thing, not a bad one -- it shows you you've made a mistake! Might even be you wouldn't have caught that mistake at all otherwise. How could it not be better to catch a mistake right as you made it and are still in the maintenance window to fix it, than to have it lingering undetected, getting ever more corrupt data, until it rears its head possibly years later, when some vital report shows your DB is full of garbage?
• PaulHoule 4y 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 4y ago
    That's not how you migrate. You disable the foreign keys, load the data, then enable them (which checks everything is ok).
• __d 4y 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.
• cratermoon 4y 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.
• josephcsible 4y ago
  Giving up foreign key constraints because they cause errors is basically the same mistake that the monk in http://thecodelesscode.com/case/115 http://thecodelesscode.com/case/115 made.
  • Rexxar 4y ago
    I didn't remember this little stories where sometimes so cruel.
  • metadat 4y 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.
• alberth 4y 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 4y ago
    "Relational" comes from https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Relational_model Table = relation
    • IshKebab 4y 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 https://en.wikipedia.org/wiki/Finitary_relation#Example I always thought it was referring to foreign keys too. Pretty bad name in hindsight.
      • zupa-hu 4y ago
        Thanks for elaborating on this! I’ve learned something today. I couldn’t make up a more misleading name if my life depended on it.. EDIT: Reading the page in more detail, it seems to me relational means both. Indeed each field of a row has a relationship (they belong to the same row after all), but there are other kinds of relationships like one-to-many achieved by foreign keys. Eg.: each row of a class table corresponds to a class, and a class corresponds to multiple students, so the relationship between the class table and the student table is "one to many"
• y1426i 4y 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.
• aaron695 4y ago
• Topgamer7 4y 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)
  • deleted 4y ago
    [deleted]
  • tuatoru 4y 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 4y ago
    Isn't that just storing the value in a normal column?
    • Topgamer7 4y 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.
      • housecarpenter 4y ago
        In the RDBMSes I'm aware of, you can disable foreign key checks while still adding the constraints themselves---they won't do anything as far as the DBMS is concerned, but can presumably still be picked up by whatever tool you are using to generate this link information.
• ivraatiems 4y 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.
  • jve 4y ago
    > 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 just can't wrap my head around the performance statement. You know better join algorithms that SQL Server is capable of (Loop/Hash/Merge)? Given that you have appropriate indexes in place, perhaps query hints is what you need to control sql plan guides if you know a thing or two about your data and it's distribution more than the sql statistics.
• scottcodie 4y 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 4y ago
    Could you give us a simple example?
• dbsmith83 4y 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
• devmor 4y 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 4y ago
    This: "You can always rewrite software. Rewriting bad data is not only difficult but often impossible."
  • charcircuit 4y 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.
    • CRConrad 4y 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.]
    • dmak 4y 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.
• takhchismail 4y ago
  [dead]
• gigatexal 4y 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.
• pram 4y 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.
• silisili 4y 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 4y 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 4y 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.
• sebazzz 4y ago
  Besides the other reasons mentioned, FK's are also implicit documentation - even if you disable them.
• mtts 4y 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.
  • tluyben2 4y 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 4y 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.
• KronisLV 4y ago
  I think that this would make a good HN poll, for example: https://news.ycombinator.com/item?id=21231804 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 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/ https://www.dbvis.com/
• habibur 4y 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 4y ago
    > I create all fields as NOT NULL and use empty string in place of NULL. ...but why?
    • habibur 4y 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.
      • sgjohnson 4y ago
        And how do you differentiate between an absence of value and empty value? it sounds like you're just using the database wrong.
        • habibur 4y ago
          That's the fundamental argument supporting the use of NULL. Extending this one will require you to use "infinite types of NULLs". Decades ago there was an article written explaining it, and that turned into a meme for a while. For example : Guess you set up a "collector database" that collects data from other databases. It might don't know what the other database's field value is, (call this situation "NULL", the classic case). Or know the other database field value and it's NULL on the other database (now call it NULL-KNOWN, or NULL-TYPE2). And then do the same thing for a program that now reads from this "collector database ". NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data. See where's that going? Use a separate field if you want to distinguish.
• jrumbut 4y 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 4y 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 4y ago
      RDBMSes don't do a lot of extra stuff, if they throw an error it's usually for a reason.
• wingshayz 4y 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 4y 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.
• floppydisc 4y 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
• iforgotpassword 4y 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.
• hknmtt 4y ago
  Never did. Never saw any need for it.
• Philip-J-Fry 4y 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 4y 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 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 4y 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.
      • connormcd 4y ago
        What kind of lock contention is that? (In Oracle) you'd have to be (a) manipulating the values of a primary key and (b) choosing not to index the child FK column. Even without (b) I'd be asking "Why are you altering primary keys?" because it pretty much aint a primary key anymore if you're doing that :-)
• Kelex 4y ago
  [dead]
• buro9 4y 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.
  • masklinn 4y 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.
  • buro9 4y 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 4y 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 4y 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.
• CraigJPerry 4y 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 4y 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.
  • deleted 4y ago
    [deleted]
• jokethrowaway 4y 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.
• stormdennis 4y ago
  I always felt slightly ashamed for the fact that I don't use foreign keys. I feel better having read this thread!
  • CRConrad 4y 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.
• mattewong 4y 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 4y 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.
• hestefisk 4y ago
  Yes, always.
• thefz 4y 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.
• thatjoeoverthr 4y 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.
  • UglyToad 4y ago
    You're exactly right. This is the crux of it. In many domains (not every domain, but every one I've ever worked in) you can delete/rewrite/change the app code without much fuss but the data is vital. For that reason having constraints enforced by the system that stores the data, external to the app code which developers will inevitably mess up, is so useful and important. So many issues in my experience have been similar to the one you describe. If the right constraints had been present so much work and so many headaches could have been avoided. I feel like people who claim they're not needed or not important or "the app code will do it" need to be wrangled into maintenance work of old systems for a year or two until they repent. Rather than boshing out an ill-considered prototype and then moving on before the bugs are discovered.
  • P5fRxh5kUvp2th 4y ago
    a few weeks ago I had a discussion with a developer with 20+ years experience about why you shouldn't squelch DB errors and try to continue running. Nothing is a hard and fast rule, but in this case the only way for an error to occur is if the query schema differed from the code select statement (column names changed). At that point wtf are you doing trying to keep running without errors, something is fundamentally mismatched between your application and the data.
  • yuvalr1 4y ago
    Key statement: integrity of the state is much more important than some inconveniences here and there
• throwawayffffas 4y 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.
• bottled_poe 4y ago
  Yes, until performance prohibits. But that’s usually in the billions of records or more.
• golondon 4y 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-266027731 https://github.com/github/gh-ost/issues/331#issuecomment-266...
  • glasshug 4y 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 4y 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-high-volume-postgresql/ https://www.braintreepayments.com/blog/safe-operations-for-h... this article has a good guideline on this.
• mejutoco 4y 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.
• oxff 4y 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..
• kureikain 4y 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 4y ago
    It’s can become pretty annoying if you want to alter your database schema.
    • astura 4y ago
      How?
• sverhagen 4y 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!)
• lisperforlife 4y 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.
• chasil 4y 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.
• forinti 4y 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.
• eric4smith 4y 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)
• QuiCasseRien 4y ago
  "use FK, you fools!" FK maintains your database integrity. If you care about data, just use it !
• Tade0 4y 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.
• bazoom42 4y ago
  Your colleagues logic is akin to turning off the smoke detector because it starts beeping any time you barbecue indoors.
• pornel 4y 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.
• throwaway22032 4y 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.
• Simon_O_Rourke 4y 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.
• quickthrower2 4y 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.
• AdrianB1 4y 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.
• hcayless 4y 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?
• forgingahead 4y ago
  Yes - is the alternative even a thing? I feel like I'm in Wonderland suddenly.
• astura 4y 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.
• Canada 4y 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.
• drderidder 4y 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.
• vbezhenar 4y 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.
• SnowHill9902 4y ago
  Are you trolling?
• nablaone 4y 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.
• pards 4y 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.
• samatman 4y 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 4y ago
    best to use tools for this such as https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape or gh-ost for mysql
• viraptor 4y 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.
• brightball 4y 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.
• Rudism 4y 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.
• pif 4y 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.
  • mkeedlinger 4y ago
    I agree that using foreign key constraints is the right choice, but the tone of your comment comes off as very condescending and dismissive, and I don't like it.
  • autarch 4y ago
    The "R" stands for "relations", as in "relations", which is a mathematical concept. SQL calls a "relation" a "table". The "relational" is RDBMS has nothing to do with relationships. But I still agree that OP's colleague is an idiot.
    • goto11 4y ago
      To be really pedantic, tables are relations but a join between two tables are also a relation. Base tables, queries and views are all relations and therefore interchangeable in relational algebra.
    • hu3 4y ago
      That might have been how it started (https://www.ibm.com/ibm/history/ibm100/us/en/icons/reldb/ https://www.ibm.com/ibm/history/ibm100/us/en/icons/reldb/). But it's definitely not what it means for the great majority of contemporary contexts. Relations in modern RDBMS are usually aliases to foreign keys unless otherwise specified.
  • wahnfrieden 4y ago
    postgres has a shitload of useful features that are unrelated to relations
  • 4y ago
• wahnfrieden 4y 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.
• darepublic 4y 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.
• m-p-3 4y ago
  Looks like your colleague wants to ultimately manage a NoSQL server, and doesn't fully understands RDBMS'es.
• sethammons 4y 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.
• zzzeek 4y 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 4y 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.
• bartread 4y 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-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 4y 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).
• JohnDeHope 4y 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.
• dedomena 4y 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.
• hk1337 4y 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.
• alphabettsy 4y 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.
• jenscow 4y 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.
• Cody_C 4y 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.
• skyde 4y 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.
• amtamt 4y ago
  from https://thedailywtf.com/articles/directive-595 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
• ryanisnan 4y 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.