5 ms·

Rails tried to hide SQL so much that one of the consequences was not supporting proper table level constraints (FK) till AR ~4.2 or so. Just take a look at the

Rails tried to hide SQL so much that one of the consequences was not supporting proper table level constraints (FK) till AR ~4.2 or so. Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations.

jgeraert 9y ago
Seeing this as well in proprietary software sold by one of the biggest erp software vendors... And it's still not fixed and likely won't be fixed anytime soon.
- mannykannot 9y ago
  The relational model provides a pretty general and unified way to represent information, together with a cohesive and powerful set of primitives for working with it, yet many systems architects insist on hiding it behind an ad-hoc interface that looks like a throwback to pre-relational days.
- moron4hire 9y ago
  There is a perception amongst lower programmers that FKs are only a thing you enable in production, that they are an impediment to rapid iteration. I mean, it's completely wrong, I know that. But try being the new guy telling a team of 10 people that. There is also a perverse corruption of "if it ain't broke, don't fix it" that goes on. If you can spend 100 hours manually validating every relationship in your application code, that's 100 hours you can put into your estimate and you know you can complete. If you only have a cursory understanding of SQL, then "learn more about FKs and implement them across the DB" seems like a big, unknowable blob of time that is impossible to estimate. It doesn't matter that it might only be 5 hours of work, at least we can be certain about 100 hours and bill the client for it. Finally, there are a small set of things that are fundamentally wrong about all modern RDBMS implementations. For example: it's nonsensical to have a foreign key that isn't indexed. You always want an index on foreign keys, there is never a scenario where you don't want them. But while primary keys are indexed by default, foreign keys are not. And those sorts of things give the anti-RDBMS crowd enough of a foot-hold to argue for continued ignorance.
  - mattbroekhuis 9y ago
    I have found out the hard way that foreign keys are indexed by default in some implementations including MySQL innodb. Not in oracle. My experience has been the opposite of yours -- I've heard of disabling foreign keys in production only for performance reasons, but not the other way around. I like them turned on all the time as well. Valuable safety rail
  - aidos 9y ago
    Curious, this doesn't fit my mental model: it's nonsensical to have a foreign key that isn't indexed I guess you normally want indexes on parent - child tree relationships (book -> chapter), but you don't want them all the time. What about when you have an 'article' with a 'status and a 'category' and you only ever find all articles by combination of category and status? In that case you'd be maintaining 3 indexes, category, status and status_category, but the only one you'd need would be status_category. Indexes are a tool to to allow for optimising lookups while foreign keys are a tool to allow you to keep your data consistent.
    - moron4hire 9y ago
      I don't see how your example doesn't require an index, specifically because, as you said, indexes are a tool to allow for optimizing lookups. I imagine that maybe you're suggesting you first query the DB for the ID of the combined status_category, and then query the article by that ID. That's not a good idea, for a couple of reasons. First, you're making two round trips to the DB when you could, with no additional effort (just effort in a different place) be doing one. Second, you've introduced a data race condition. If someone deletes that status_category after you've queried for it but before you've queried the articles, you aren't going to get the results you want. It would be better to do a join across articles to status_category to status and category, then query based on the status and category values you want. Without an index on the FKs between status_category and status and category, a relatively small table can have a big impact on query performance. Finally, while I know your example is arbitrary, it's a little hard to argue against a design that is probably wrong. I doubt the suggested schema for articles and categories is a good one. If I argue "you should never have to arbitrarily subtract '1' from a result just to get the results you want", it would not be a good counterargument to say, "yes, but sometimes you want to add 2 and 2 and get 5, so then you need to subtract 1". The problem isn't where you see it. FKs aren't just a consistency tool. Consistency and referential integrity are features that results from having an FK, but the FK is a signal that data can be searched in a certain way.
jmfurlott 9y ago
This fact alone, after several years of changing schema and migrations, led to us having major rewrites and porting over to much more hands off approach. It's an understatement how much AR has led to just confusion especially with new employees!
elsurudo 9y ago
That's true, but you could always either create them yourself in SQL in a migration, or use a gem like foreigner (as I did). Always use FKs...
ad-hominem 9y ago
> Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations. This is a failure of the programmers. Rails itself didn't really support foreign keys, but relationships did have an option to destroy any child tuples when the parent is destroyed (culling orphans). This sounds like it wasn't enabled when it should have been.
geebee 9y ago
I regularly use raw sql in rails. ORMs are fine for convenience, but for me, they never make or break an app. They can save you some typing on very basic stuff, but if you find you're writing complex ORM code, I think it's a good idea to just use sql directly.

jgeraert 9y ago

Seeing this as well in proprietary software sold by one of the biggest erp software vendors... And it's still not fixed and likely won't be fixed anytime soon.

mannykannot 9y ago
The relational model provides a pretty general and unified way to represent information, together with a cohesive and powerful set of primitives for working with it, yet many systems architects insist on hiding it behind an ad-hoc interface that looks like a throwback to pre-relational days.
moron4hire 9y ago
There is a perception amongst lower programmers that FKs are only a thing you enable in production, that they are an impediment to rapid iteration. I mean, it's completely wrong, I know that. But try being the new guy telling a team of 10 people that. There is also a perverse corruption of "if it ain't broke, don't fix it" that goes on. If you can spend 100 hours manually validating every relationship in your application code, that's 100 hours you can put into your estimate and you know you can complete. If you only have a cursory understanding of SQL, then "learn more about FKs and implement them across the DB" seems like a big, unknowable blob of time that is impossible to estimate. It doesn't matter that it might only be 5 hours of work, at least we can be certain about 100 hours and bill the client for it. Finally, there are a small set of things that are fundamentally wrong about all modern RDBMS implementations. For example: it's nonsensical to have a foreign key that isn't indexed. You always want an index on foreign keys, there is never a scenario where you don't want them. But while primary keys are indexed by default, foreign keys are not. And those sorts of things give the anti-RDBMS crowd enough of a foot-hold to argue for continued ignorance.
- mattbroekhuis 9y ago
  I have found out the hard way that foreign keys are indexed by default in some implementations including MySQL innodb. Not in oracle. My experience has been the opposite of yours -- I've heard of disabling foreign keys in production only for performance reasons, but not the other way around. I like them turned on all the time as well. Valuable safety rail
- aidos 9y ago
  Curious, this doesn't fit my mental model: it's nonsensical to have a foreign key that isn't indexed I guess you normally want indexes on parent - child tree relationships (book -> chapter), but you don't want them all the time. What about when you have an 'article' with a 'status and a 'category' and you only ever find all articles by combination of category and status? In that case you'd be maintaining 3 indexes, category, status and status_category, but the only one you'd need would be status_category. Indexes are a tool to to allow for optimising lookups while foreign keys are a tool to allow you to keep your data consistent.
  - moron4hire 9y ago
    I don't see how your example doesn't require an index, specifically because, as you said, indexes are a tool to allow for optimizing lookups. I imagine that maybe you're suggesting you first query the DB for the ID of the combined status_category, and then query the article by that ID. That's not a good idea, for a couple of reasons. First, you're making two round trips to the DB when you could, with no additional effort (just effort in a different place) be doing one. Second, you've introduced a data race condition. If someone deletes that status_category after you've queried for it but before you've queried the articles, you aren't going to get the results you want. It would be better to do a join across articles to status_category to status and category, then query based on the status and category values you want. Without an index on the FKs between status_category and status and category, a relatively small table can have a big impact on query performance. Finally, while I know your example is arbitrary, it's a little hard to argue against a design that is probably wrong. I doubt the suggested schema for articles and categories is a good one. If I argue "you should never have to arbitrarily subtract '1' from a result just to get the results you want", it would not be a good counterargument to say, "yes, but sometimes you want to add 2 and 2 and get 5, so then you need to subtract 1". The problem isn't where you see it. FKs aren't just a consistency tool. Consistency and referential integrity are features that results from having an FK, but the FK is a signal that data can be searched in a certain way.

jmfurlott 9y ago

This fact alone, after several years of changing schema and migrations, led to us having major rewrites and porting over to much more hands off approach. It's an understatement how much AR has led to just confusion especially with new employees!

elsurudo 9y ago

That's true, but you could always either create them yourself in SQL in a migration, or use a gem like foreigner (as I did). Always use FKs...

ad-hominem 9y ago

> Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations. This is a failure of the programmers. Rails itself didn't really support foreign keys, but relationships did have an option to destroy any child tuples when the parent is destroyed (culling orphans). This sounds like it wasn't enabled when it should have been.

geebee 9y ago

I regularly use raw sql in rails. ORMs are fine for convenience, but for me, they never make or break an app. They can save you some typing on very basic stuff, but if you find you're writing complex ORM code, I think it's a good idea to just use sql directly.