3 ms·

> 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 wo

> 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?
- JoelJacobson 5y ago
  When I wrote that comment, the idea had even more flaws than currently, at that time I suggested using "WITH" and a new "->" operator. Thanks to new ideas coming from other users in the PostgreSQL and Hacker News community, those problems have been solved, and we now have less remaining problems with the proposal. I'm really grateful for all the help. Like I said in another reply, I will put together a "Drawbacks / Remaining issues" section and update the Gist, based on all replies. Perhaps the end result will be Status Quo, but at least then we have documented the reasons why this idea is a dead end. However, thanks to all the improvements just during the last couple of days, I feel really optimistic and motivated, so I think there is a great chance we can solve the remaining issues together if we try. To comment on the response from the direct/hash person: The point made by the user, "not currently any SQL statements that depend on structural information as defined in the foreign key relationships", is true, but I don't see why that's an argument by itself against the idea? I find the other argument, claiming there would be a problem with tooling and processes, much more interesting and I'm eager to fully understand it. I asked a question in hope to do so, "what do you suggest would be the problem from the tools perspective", but has so far not received any reply.
  - mtone 5y ago
    I also agree that relying on database schema is a show stopper. First counterpoint: As long as data type information isn't completely messed up, I can dump excel spreadsheets in a database, or dump database CSVs in a data lake, and start querying them right away using SQL with complex joins using auto-completion from the dataset alone. Second counterpoint (harder to communicate): I work with a SaaS database (MS Dynamics/Dataverse) which doesn't provide direct SQL access and is not supported by common ORMs. Some of the data APIs require relationship schema information which invariably put a needle in attempts to generalize functionality, or just consume. In this context, creating a simple in-memory test database or serializing records between modules, cannot possibly function without also knowing schema information if queries are going to make use of it. So now you need to carry schema information for everything, and load it at run-time from generated code or a live database, in back-end and front-end, just to interact with data where simple SQL would have worked just fine. Conclusion: I dislike the proposal -- even if not breaking backward compatibility, it brings database-configuration details to SQL. SQL is imperfect and is already hurt by database-implementation details (like date functions) but it remains a beautiful expression of relational algebra and set theory - a query given the same data should return the same output, regardless of context. SQL is lingua franca for a reason. The proposal feels like a fairly specific developer-centric extension and isn't where SQL should be headed, in my opinion.

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?

JoelJacobson 5y ago
When I wrote that comment, the idea had even more flaws than currently, at that time I suggested using "WITH" and a new "->" operator. Thanks to new ideas coming from other users in the PostgreSQL and Hacker News community, those problems have been solved, and we now have less remaining problems with the proposal. I'm really grateful for all the help. Like I said in another reply, I will put together a "Drawbacks / Remaining issues" section and update the Gist, based on all replies. Perhaps the end result will be Status Quo, but at least then we have documented the reasons why this idea is a dead end. However, thanks to all the improvements just during the last couple of days, I feel really optimistic and motivated, so I think there is a great chance we can solve the remaining issues together if we try. To comment on the response from the direct/hash person: The point made by the user, "not currently any SQL statements that depend on structural information as defined in the foreign key relationships", is true, but I don't see why that's an argument by itself against the idea? I find the other argument, claiming there would be a problem with tooling and processes, much more interesting and I'm eager to fully understand it. I asked a question in hope to do so, "what do you suggest would be the problem from the tools perspective", but has so far not received any reply.
- mtone 5y ago
  I also agree that relying on database schema is a show stopper. First counterpoint: As long as data type information isn't completely messed up, I can dump excel spreadsheets in a database, or dump database CSVs in a data lake, and start querying them right away using SQL with complex joins using auto-completion from the dataset alone. Second counterpoint (harder to communicate): I work with a SaaS database (MS Dynamics/Dataverse) which doesn't provide direct SQL access and is not supported by common ORMs. Some of the data APIs require relationship schema information which invariably put a needle in attempts to generalize functionality, or just consume. In this context, creating a simple in-memory test database or serializing records between modules, cannot possibly function without also knowing schema information if queries are going to make use of it. So now you need to carry schema information for everything, and load it at run-time from generated code or a live database, in back-end and front-end, just to interact with data where simple SQL would have worked just fine. Conclusion: I dislike the proposal -- even if not breaking backward compatibility, it brings database-configuration details to SQL. SQL is imperfect and is already hurt by database-implementation details (like date functions) but it remains a beautiful expression of relational algebra and set theory - a query given the same data should return the same output, regardless of context. SQL is lingua franca for a reason. The proposal feels like a fairly specific developer-centric extension and isn't where SQL should be headed, in my opinion.