6 ms·

As someone currently fighting a battle with an LoB application written without foreign-key constraints with hundreds of thousands of rows of corrupted data beca

As someone currently fighting a battle with an LoB application written without foreign-key constraints with hundreds of thousands of rows of corrupted data because of bugs in sprocs that assigned the wrong value to the wrong foreign key column because they were similarly named - THIS! The reply in the GitHub thread we’re talking about makes it clear that they still perform FK validation - it’s just performed in the application code rather than the DBMS. I note there is another alternative: deferred constraints - or just run a query to check for invalid rows at 3am every morning.

spookthesunset 7y ago
The application code isn’t enforcing FK constraints because that is impossible for the application to do. Their database is almost 100% guaranteed to be corrupt as a result. Application code has bugs. Application code can fail in ways that result in corruption. A primary job of the database is to keep itself from getting corrupted. Enforcing foreign key violations is only something the database can do correctly. Punting the responsibility to a higher layer will result in corruption.
- GauntletWizard 7y ago
  There are 100% foreign key violations in their database. That is not the same as their database being corrupt. They have engineered for, and understand the implications of, foreign key violations. Typically, it's as simple as "This row can be deleted", and that can cascade - at a totally different rate than you'd find in a database and with totally different performance characteristics.
  - spookthesunset 7y ago
    Foreign key violations are data corruption!!! It violates the rules of how the data relates and can and will screw up any number of things that depend on the rules being enforced. Reporting and bi data might get hosed. Account management might get hosed. Who knows what happens when FK rules are violated because by definition they should never be violated. It puts all applications on top into a undefined state, leading to bugs and god knows what else. Foreign key violations are 100% data corruption.
    - SkyPuncher 7y ago
      > It violates the rules of how the data relates You're missing an important point, it violates a certain set of rules of how the data relates. That certain set of rules typically makes it easier to write most general data applications. If your rules define a foreign key as optional, you can easily engineer an application around it. Great examples include NoSQL and Ruby duck-typing. In both cases, you infer actions based on the data structure, rather than making explicit assumptions.
      - Aeolun 7y ago
        The whole fact that you made a foreign key implies that that is now a rule of your database. If anything is in that column that does not adhere to the foreign key, that means your database is corrupted.
        lugg 7y ago
        Yes, and what if you don't make a foreign key?
        spookthesunset 7y ago
        Then you have an implicit rule that is enforced by a hope and a prayer that some junior dev never makes a mistake, your senior engineers are clairvoyant and understand every single aspect of your systems 100% with zero off-days, your code review process catches every single possible edge case (especially the edge cases that you never knew existed), your QA process is 100% and never makes mistakes, your servers never crash in ways that leave things in an inconsistent state, etc. Or you could, you know, simply add a foreign key constraint and never, ever, ever have corrupt table relations. Why people fight their tools is beyond me. There is almost zero reason to defend not using foreign keys.
        jerzyt 7y ago
        That's exactly my experience and viewpoint.
- hrktb 7y ago
  > A primary job of the database is to keep itself from getting corrupted That would be where the road splits. If you handle the database as a very fast and structured storage application, a lot of these assumptions go away, with then a different set of tradeoffs. Some data corruption could be fine if you can guarantee the critical cases, just as bugs in the code are fine as long as the useful cases are covered. I remember a database with scheduling entries in it that could get duplicated depending on the sharding, but it didn’t matter because the app handled the case gracefully. I think understanding the tradeoffs that match the best the use case is the most important point, always assuming that a DB has to guarantee integrity can be a burden preventing from looking at all the options.
- lugg 7y ago
  Will result in corruption. Yes. Will result in more fault tolerant software, also yes. It's a trade off and one I make willingly at every scale. I stopped using foreign keys after university and have never wanted them since. Non nullable database fields are far more useful than worrying about fks.
  - xref 7y ago
    > Will result in more fault tolerant software More fault tolerant because you have to waste time debugging the faults and monkeypatching them in code just to avoid FKs? > Non nullable database fields are far more useful than worrying about fks Can’t even count how many non-nullable fields I’ve seen packed with “” empty strings to get around that requirement
    - lugg 7y ago
      1. Over my career almost none of it has been debugging problems due to a lack of foreign keys. Further to that, instead of having lazy cascading deletes moving the goal posts on you. Data missing its parent for example is an indication something is wrong. It's a useful diagnostic. 2. Most string data is safe to represent as empty. Handling null on the other hand is a different situation and often induces warnings or other side effects depending on the language. Forcing the null checking to your boundaries is much like forcing your state/mutation to the boundaries in FP. Hell eveb though I think it's insane, hexagonal arch works on this idea as well.
sturgill 7y ago
I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app. And the “Rails Way” is to create these constraints in the model. Foreign key constraints weren’t a first-class member in Rails until v4 (if memory serves correctly). I was once a full-time Rails dev and really loved the framework (I don’t write as many user facing applications these days). Most of the Omakase trade offs didn’t bother me. But I never understood the disdain for foreign keys. For 99.99% of web apps, you want them (dare I say, need them). Even in Rails 3 I would add them by hand in the migration files. Very few applications will ever actually care about sharding. We were pulling in millions at the last Rails company I worked for and were just fine with a master and a single replica. If you get to a point where sharding is best for your company you hopefully have enough revenue coming in to fund a data transition. Your goal should be to outgrow what MySQL (or Postgres) can do for you in master/replica mode. If you do, you’ll likely be independently wealthy... If data integrity matters at all, model-based checks (or periodic queries for orphaned data) will not suffice. Just put a foreign key in the table where it belongs and let your DB do what it does best. ACID is an amazing thing...
- hrktb 7y ago
  It’s at its core a case by case decision, I think FK are also a net negative in data ingestion scenari where the data set is big enough. Trying to make sure everything is where it needs to be at any given time, everything is inserted in the right order and the data is always consistent brings exponential amount of conplexity when it could all be checked at the end and pruned for invalid data. And usually DB integrity will not be enough, you’ll want business level validation that all is OK, so there will be app level checks anyway.
  - joevandyk 7y ago
    PostgreSQL supports deferring constraints. https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html https://begriffs.com/posts/2017-08-27-deferrable-sql-constra...
    - hrktb 7y ago
michaelt 7y ago
> or just run a query to check for invalid rows at 3am every morning One of the nice things you get from database FK constraints is when something messes up the data, you get an exception thrown in the code that's messing it up, complete with a backtrace and whatever inputs and timestamps you care to log.

spookthesunset 7y ago

The application code isn’t enforcing FK constraints because that is impossible for the application to do. Their database is almost 100% guaranteed to be corrupt as a result. Application code has bugs. Application code can fail in ways that result in corruption. A primary job of the database is to keep itself from getting corrupted. Enforcing foreign key violations is only something the database can do correctly. Punting the responsibility to a higher layer will result in corruption.

GauntletWizard 7y ago
There are 100% foreign key violations in their database. That is not the same as their database being corrupt. They have engineered for, and understand the implications of, foreign key violations. Typically, it's as simple as "This row can be deleted", and that can cascade - at a totally different rate than you'd find in a database and with totally different performance characteristics.
- spookthesunset 7y ago
  Foreign key violations are data corruption!!! It violates the rules of how the data relates and can and will screw up any number of things that depend on the rules being enforced. Reporting and bi data might get hosed. Account management might get hosed. Who knows what happens when FK rules are violated because by definition they should never be violated. It puts all applications on top into a undefined state, leading to bugs and god knows what else. Foreign key violations are 100% data corruption.
  - SkyPuncher 7y ago
    > It violates the rules of how the data relates You're missing an important point, it violates a certain set of rules of how the data relates. That certain set of rules typically makes it easier to write most general data applications. If your rules define a foreign key as optional, you can easily engineer an application around it. Great examples include NoSQL and Ruby duck-typing. In both cases, you infer actions based on the data structure, rather than making explicit assumptions.
    - Aeolun 7y ago
      The whole fact that you made a foreign key implies that that is now a rule of your database. If anything is in that column that does not adhere to the foreign key, that means your database is corrupted.
      - lugg 7y ago
        Yes, and what if you don't make a foreign key?
        spookthesunset 7y ago
        Then you have an implicit rule that is enforced by a hope and a prayer that some junior dev never makes a mistake, your senior engineers are clairvoyant and understand every single aspect of your systems 100% with zero off-days, your code review process catches every single possible edge case (especially the edge cases that you never knew existed), your QA process is 100% and never makes mistakes, your servers never crash in ways that leave things in an inconsistent state, etc. Or you could, you know, simply add a foreign key constraint and never, ever, ever have corrupt table relations. Why people fight their tools is beyond me. There is almost zero reason to defend not using foreign keys.
        jerzyt 7y ago
        That's exactly my experience and viewpoint.
hrktb 7y ago
> A primary job of the database is to keep itself from getting corrupted That would be where the road splits. If you handle the database as a very fast and structured storage application, a lot of these assumptions go away, with then a different set of tradeoffs. Some data corruption could be fine if you can guarantee the critical cases, just as bugs in the code are fine as long as the useful cases are covered. I remember a database with scheduling entries in it that could get duplicated depending on the sharding, but it didn’t matter because the app handled the case gracefully. I think understanding the tradeoffs that match the best the use case is the most important point, always assuming that a DB has to guarantee integrity can be a burden preventing from looking at all the options.
lugg 7y ago
Will result in corruption. Yes. Will result in more fault tolerant software, also yes. It's a trade off and one I make willingly at every scale. I stopped using foreign keys after university and have never wanted them since. Non nullable database fields are far more useful than worrying about fks.
- xref 7y ago
  > Will result in more fault tolerant software More fault tolerant because you have to waste time debugging the faults and monkeypatching them in code just to avoid FKs? > Non nullable database fields are far more useful than worrying about fks Can’t even count how many non-nullable fields I’ve seen packed with “” empty strings to get around that requirement
  - lugg 7y ago
    1. Over my career almost none of it has been debugging problems due to a lack of foreign keys. Further to that, instead of having lazy cascading deletes moving the goal posts on you. Data missing its parent for example is an indication something is wrong. It's a useful diagnostic. 2. Most string data is safe to represent as empty. Handling null on the other hand is a different situation and often induces warnings or other side effects depending on the language. Forcing the null checking to your boundaries is much like forcing your state/mutation to the boundaries in FP. Hell eveb though I think it's insane, hexagonal arch works on this idea as well.

sturgill 7y ago

I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app. And the “Rails Way” is to create these constraints in the model. Foreign key constraints weren’t a first-class member in Rails until v4 (if memory serves correctly). I was once a full-time Rails dev and really loved the framework (I don’t write as many user facing applications these days). Most of the Omakase trade offs didn’t bother me. But I never understood the disdain for foreign keys. For 99.99% of web apps, you want them (dare I say, need them). Even in Rails 3 I would add them by hand in the migration files. Very few applications will ever actually care about sharding. We were pulling in millions at the last Rails company I worked for and were just fine with a master and a single replica. If you get to a point where sharding is best for your company you hopefully have enough revenue coming in to fund a data transition. Your goal should be to outgrow what MySQL (or Postgres) can do for you in master/replica mode. If you do, you’ll likely be independently wealthy... If data integrity matters at all, model-based checks (or periodic queries for orphaned data) will not suffice. Just put a foreign key in the table where it belongs and let your DB do what it does best. ACID is an amazing thing...

hrktb 7y ago
It’s at its core a case by case decision, I think FK are also a net negative in data ingestion scenari where the data set is big enough. Trying to make sure everything is where it needs to be at any given time, everything is inserted in the right order and the data is always consistent brings exponential amount of conplexity when it could all be checked at the end and pruned for invalid data. And usually DB integrity will not be enough, you’ll want business level validation that all is OK, so there will be app level checks anyway.
- joevandyk 7y ago
  PostgreSQL supports deferring constraints. https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html https://begriffs.com/posts/2017-08-27-deferrable-sql-constra...
  - hrktb 7y ago

michaelt 7y ago

> or just run a query to check for invalid rows at 3am every morning One of the nice things you get from database FK constraints is when something messes up the data, you get an exception thrown in the code that's messing it up, complete with a backtrace and whatever inputs and timestamps you care to log.