6 ms·

Please, no. A common problem in data warehousing is that a design with lots of foreign keys becomes slow to load. A standard solution is to move the checks fo

by btilly 5y ago

Please, no. A common problem in data warehousing is that a design with lots of foreign keys becomes slow to load. A standard solution is to move the checks for referential integrity elsewhere, then drop the foreign key constraint. This massively improves load performance. This syntax change means that this solution can't be used because you have no idea what random queries out there might rely on the specific existence of a foreign key constraint for the definition of the query. Thereby meaning that if a foreign key constraint becomes a performance problem, we're stuck with it rather than having a solution. Features have consequences. And I don't like the consequences of making business rules that are now explicit in the query, be instead implicit in the table design.

JoelJacobson 5y ago
Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably good in general too, but I agree you might need to disable it for some FKs, in some databases, like PostgreSQL before they got FOR KEY SHARE locks.
- hn_throwaway_99 5y ago
  At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself. Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen.
  - JoelJacobson 5y ago
    > Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. > So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen. Why would it be a problem from the tools perspective if the foreign key wasn't actually enforced if the DBA insists on temporarily disabling the enforcement of the FK? If the tool would e.g. be used to insert a row, and the DB would accept it, even though it would violate the FK, what do you suggest would be the problem from the tools perspective? This is also not a new idea. It's already implemented in MSSQL, see WITH NOCHECK.
    - deleted 5y ago
      [deleted]
    - hn_throwaway_99 5y ago
      > Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. The point that everyone is making is there are not currently any SQL statements that depend on structural information as defined in the foreign key relationships when calculating the structure of the data. Furthermore, there are already tons of tooling and processes that depend on this fact, that your proposal would break, for a teeny bit of less typing. Beating a dead horse at this point.
    - tqi 5y ago
      The person you are responding to was more direct/harsh than was necessary, but I think it would be good to step back for a moment and reflect on the feedback from this community. You said "If someone can convince me this is a bad idea, that would help me forget about all of this, so I would greatly appreciate your thoughts, no matter how negative or positive." I think there are enough valid objections here to at least consider the idea that this is not a clear improvement?
cogman10 5y ago
Absolutely agree. Further, whenever someone is adjusting table performance index tweaks is almost always the first thing to tackle. Adding foreign keys into the query is just as bad as adding indexes into the query (which, you can do in T-SQL, but generally shouldn't). Indexes can be dropped, changed, or added and you SHOULD be relying on the SQL optimizer to use the most appropriate index. This feature appears to only save a bit of typing in the best of scenarios. In the worst, an update/drop of a foreign key will end up breaking a bunch of queries, which is insane.
- Tostino 5y ago
  While I wish there were a way to easily refactor queries when making these changes, this is not different than it works for renaming/dropping a column..."In the worst, an re-name/drop of a column will end up breaking a bunch of queries, which is insane."
  - cogman10 5y ago
    I'd argue it's about expectations. If I change the shape of my data, of course I'd expect a bunch of stuff that potentially needs to be updated. Adding and removing fields is dangerous. On the flip side, changing an index or a foreign key will almost never change any query, because the data shape is exactly the same. The most it will effect is queries that change data. Even then, when you make that change you have to address, up front, what to do with existing data that might violate a new constraint.
zzzeek 5y ago
this was my thought too. but now you need an ALTER CONSTRAINT NON ENFORCING or something like that so that the "constraints" can be present declaratively but don't actually get used. which then looks a whole lot like you're just introducing macros into SQL where you have some symbolic keywords that expand out into pre-fabricated ON clauses.
seadan83 5y ago
This, the result of a query should not change depending on a constraint.
- zmmmmm 5y ago
  By that do you mean, should not vary in the data returned or should not break? Personally I agree that changing a constraint shouldn't alter the data returned. But I'm happy enough if it breaks in a clear and verifiable manner. There are plenty of other situations where adding a constraint will cause existing SQL (if not queries) to break so its not really that much of a change.
zmmmmm 5y ago
Data warehouses have all sorts of different design approaches based on their different requirements, wouldn't this just be another one of them? I actually think that having a way to alter the constraint to non-enforcing sounds like it'd be better for your situation anyway because you can then have your load performance but utilise the constraints with background / delayed process that checks the integrity because the information is still there in a standard form. Which sort of leads to .... I don't agree with your characterisation of foreign key constraints as business rules. They are genuine information about the structure of the data.
tapas73 5y ago
Lets have disabled FK then. postgre, seems to not have it, but the proposal could include also "disabled FK" part. teradata, oracle, sql server already have option for FK "disable/no check". https://docs.teradata.com/r/eWpPpcMoLGQcZEoyt5AjEg/df1PvVh6eYgXL6BdS1xEng https://docs.teradata.com/r/eWpPpcMoLGQcZEoyt5AjEg/df1PvVh6e... https://docs.microsoft.com/en-us/sql/relational-databases/tables/disable-foreign-key-constraints-with-insert-and-update-statements?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/ta... https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11546 https://docs.oracle.com/cd/B28359_01/server.111/b28310/gener...
cryptonector 5y ago
Enforcement of relational integrity on bulk load should be optional -- you trust the data, right? But also, if you want relational integrity, then you have to check it somewhere. Lastly, this isn't about DML or bulk load but about query expressivity. You could easily have FOREIGN KEY constraints where the RDBMS is also instructed not to enforce relational integrity, either on bulk load or at all, and documenting the FK in the schema is still massively useful.

by btilly 5y ago

JoelJacobson 5y ago

Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably good in general too, but I agree you might need to disable it for some FKs, in some databases, like PostgreSQL before they got FOR KEY SHARE locks.

hn_throwaway_99 5y ago
At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally. I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself. Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen.
- JoelJacobson 5y ago
  > Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints. Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. > So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen. Why would it be a problem from the tools perspective if the foreign key wasn't actually enforced if the DBA insists on temporarily disabling the enforcement of the FK? If the tool would e.g. be used to insert a row, and the DB would accept it, even though it would violate the FK, what do you suggest would be the problem from the tools perspective? This is also not a new idea. It's already implemented in MSSQL, see WITH NOCHECK.
  - deleted 5y ago
    [deleted]
  - hn_throwaway_99 5y ago
    > Similar to how JOIN FOREIGN would depend on the structure of the data model, defined by tables, foreign keys, etc. The point that everyone is making is there are not currently any SQL statements that depend on structural information as defined in the foreign key relationships when calculating the structure of the data. Furthermore, there are already tons of tooling and processes that depend on this fact, that your proposal would break, for a teeny bit of less typing. Beating a dead horse at this point.
  - tqi 5y ago
    The person you are responding to was more direct/harsh than was necessary, but I think it would be good to step back for a moment and reflect on the feedback from this community. You said "If someone can convince me this is a bad idea, that would help me forget about all of this, so I would greatly appreciate your thoughts, no matter how negative or positive." I think there are enough valid objections here to at least consider the idea that this is not a clear improvement?

cogman10 5y ago

Absolutely agree. Further, whenever someone is adjusting table performance index tweaks is almost always the first thing to tackle. Adding foreign keys into the query is just as bad as adding indexes into the query (which, you can do in T-SQL, but generally shouldn't). Indexes can be dropped, changed, or added and you SHOULD be relying on the SQL optimizer to use the most appropriate index. This feature appears to only save a bit of typing in the best of scenarios. In the worst, an update/drop of a foreign key will end up breaking a bunch of queries, which is insane.

Tostino 5y ago
While I wish there were a way to easily refactor queries when making these changes, this is not different than it works for renaming/dropping a column..."In the worst, an re-name/drop of a column will end up breaking a bunch of queries, which is insane."
- cogman10 5y ago
  I'd argue it's about expectations. If I change the shape of my data, of course I'd expect a bunch of stuff that potentially needs to be updated. Adding and removing fields is dangerous. On the flip side, changing an index or a foreign key will almost never change any query, because the data shape is exactly the same. The most it will effect is queries that change data. Even then, when you make that change you have to address, up front, what to do with existing data that might violate a new constraint.

zzzeek 5y ago

this was my thought too. but now you need an ALTER CONSTRAINT NON ENFORCING or something like that so that the "constraints" can be present declaratively but don't actually get used. which then looks a whole lot like you're just introducing macros into SQL where you have some symbolic keywords that expand out into pre-fabricated ON clauses.

seadan83 5y ago

This, the result of a query should not change depending on a constraint.

zmmmmm 5y ago
By that do you mean, should not vary in the data returned or should not break? Personally I agree that changing a constraint shouldn't alter the data returned. But I'm happy enough if it breaks in a clear and verifiable manner. There are plenty of other situations where adding a constraint will cause existing SQL (if not queries) to break so its not really that much of a change.

zmmmmm 5y ago

Data warehouses have all sorts of different design approaches based on their different requirements, wouldn't this just be another one of them? I actually think that having a way to alter the constraint to non-enforcing sounds like it'd be better for your situation anyway because you can then have your load performance but utilise the constraints with background / delayed process that checks the integrity because the information is still there in a standard form. Which sort of leads to .... I don't agree with your characterisation of foreign key constraints as business rules. They are genuine information about the structure of the data.

tapas73 5y ago

Lets have disabled FK then. postgre, seems to not have it, but the proposal could include also "disabled FK" part. teradata, oracle, sql server already have option for FK "disable/no check". https://docs.teradata.com/r/eWpPpcMoLGQcZEoyt5AjEg/df1PvVh6eYgXL6BdS1xEng https://docs.teradata.com/r/eWpPpcMoLGQcZEoyt5AjEg/df1PvVh6e... https://docs.microsoft.com/en-us/sql/relational-databases/tables/disable-foreign-key-constraints-with-insert-and-update-statements?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/ta... https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11546 https://docs.oracle.com/cd/B28359_01/server.111/b28310/gener...

cryptonector 5y ago

Enforcement of relational integrity on bulk load should be optional -- you trust the data, right? But also, if you want relational integrity, then you have to check it somewhere. Lastly, this isn't about DML or bulk load but about query expressivity. You could easily have FOREIGN KEY constraints where the RDBMS is also instructed not to enforce relational integrity, either on bulk load or at all, and documenting the FK in the schema is still massively useful.