Thread: Foreign key joins revisited
Hi,
I've revisited the idea to somehow use foreign keys to do joins,
in the special but common case when joining on columns that exactly match a foreign key.
The idea is to add a new ternary operator, which would be allowed only in the FROM clause.
It would take three operands:
1) referencing_table_alias
2) foreign_key_constraint_name
3) referenced_table_alias
POSSIBLE BENEFITS
* Eliminate risk of joining on the wrong columns
Although probably an uncommon class of bugs, a join can be made on the wrong columns, which could go undetected if the desired row is included by coincidence, such as if the test environment might only contain a single row in some table, and the join condition happened to be always true.
By joining using the foreign key, it can be verified at compile time, that the referenced_table_alias is actually an alias for the table referenced by the foreign key. If some other alias would be given, an error would be thrown, to avoid failure.
* Conciser syntax
In a traditional join, you have to explicitly state all columns for the referencing and referenced table.
I think writing joins feels like you are repeating the same table aliases and column names over and over again, all the time.
This is especially true for multiple-column joins.
This is somewhat addressed by the USING join form, but USING has other drawbacks, why I tend to avoid it except for one-off queries.
When having to use fully-qualified table aliases, that adds even further to the verboseness.
* Makes abnormal joins stand out
If joining on something else than foreign key columns, or some inequality expression, such joins will continue to be written in the traditional way, and will therefore stand out and be more visible, if all other foreign key-based joins are written using the new syntax.
When reading SQL queries, I think this would be a great improvement, since the boring normal joins on foreign keys could be given less attention, and focus could instead be made on making sure you understand the more complex joins.
* Explicit direction of the join
In a traditional join on foreign key columns, it's not possible to derive if the join is a one-to-many or many-to-one join, by just looking at the SQL code itself. One must also know/inspect the data model or make assumptions based on the naming of columns and tables. This is perhaps the least interesting benefit though, since good naming makes the direction quite obvious anyway. But I think it at least reduces the total cognitive load of reading a SQL query.
POSSIBLE DRAWBACKS
* Another thing users would have to learn
* Would require changes to the SQL standard, i.e. SQL committee work
* Introduces a hard dependency on foreign keys, they cannot be dropped
SYNTAX
Syntax is hard, but here is a proposal to start the discussion:
from_item join_type from_item WITH [referencing_table_alias]->[foreign_key_constraint_name] = [referenced_table_alias] [ AS join_using_alias ]
EXAMPLE
To experiment with the idea, I wanted to find some real-world queries written by others,
to see how such SQL queries would look like, using traditional joins vs foreign key joins.
I came up with the idea of searching Github for "LEFT JOIN", since just searching for "JOIN" would match a lot of non-SQL code as well.
Here is one of the first examples I found, a query below from the Grafana project [1]
SELECT
p.*,
? AS resource_id,
ur.user_id AS user_id,
u.login AS user_login,
u.email AS user_email,
tr.team_id AS team_id,
t.name AS team,
t.email AS team_email,
r.name as role_name
FROM permission p
LEFT JOIN role r ON p.role_id = r.id
LEFT JOIN team_role tr ON r.id = tr.role_id
LEFT JOIN team t ON tr.team_id = t.id
LEFT JOIN user_role ur ON r.id = ur.role_id
LEFT JOIN user u ON ur.user_id = u.id
WHERE p.id = ?
Here is how the FROM clause could be rewritten:
FROM permission p
LEFT JOIN role r WITH p->permission_role_id_fkey = r
LEFT JOIN team_role tr WITH tr->team_role_role_id_fkey = r
LEFT JOIN team t WITH tr->team_role_team_id_fkey = t
LEFT JOIN user_role ur WITH ur->user_role_role_id_fkey = r
LEFT JOIN "user" u WITH ur->user_role_user_id_fkey = u
WHERE p.id = 1;
In PostgreSQL, the foreign keys could also be given shorter names, since they only need to be unique per table and not per namespace. I think a nice convention is to give the foreign keys the same name as the referenced table, except if the same table is referenced multiple times or is self-referenced.
Rewriting our example, using such naming convention for the foreign keys:
FROM permission p
LEFT JOIN role r WITH p->role = r
LEFT JOIN team_role tr WITH tr->role = r
LEFT JOIN team t WITH tr->team = t
LEFT JOIN user_role ur WITH ur->role = r
LEFT JOIN "user" u WITH ur->user = u
WHERE p.id = 1;
A better example to illustrate how conciseness is improved, would be one with lots of multi-column joins.
Please feel free to share better query examples to evaluate.
I cannot stop thinking about this idea, I really think it would greatly improve SQL as a language.
Foreign keys feels like such an underused valuable potential resource!
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.
Thank you for digesting.
/Joel
PS.
To readers who might remember the old flawed version of this new idea:
In the old proposal, the third operand (referenced_table_alias) was missing.
There wasn't a way of specifying what table alias the join was supposed to be made against.
It was assumed the referenced table was always the one being joined in,
which is not always the case, since the referenced table
might already be in scope, and it's instead the referencing table which is being joined in.
Another problem with the old idea was you were forced to write the joins in a the same order
as the foreign keys, which often resulted in an awkward join order.
These two problems have now been solved with this new proposal.
Perhaps new problems have been introduced though?
On Saturday, December 25, 2021, Joel Jacobson <joel@compiler.org> wrote:
I've revisited the idea to somehow use foreign keys to do joins,
-1
This is somewhat addressed by the USING join form, but USING has other drawbacks, why I tend to avoid it except for one-off queries.
I find this sufficient.
* Would require changes to the SQL standard, i.e. SQL committee work
Huh?
David J.
On Sat, Dec 25, 2021, at 22:06, David G. Johnston wrote:
> On Saturday, December 25, 2021, Joel Jacobson <joel@compiler.org> wrote:
> * Would require changes to the SQL standard, i.e. SQL committee work
>
> Huh?
I mean, one could argue this is perhaps even the wrong forum to discuss this idea,
since it's a proposed change to the SQL language.
But I think it's still meaningful to discuss the idea here,
since if we can reach a consensus and work on a PoC implementation,
that would be very valuable when presenting the idea to the SQL committee.
/Joel
On Sat, Dec 25, 2021, at 21:55, Joel Jacobson wrote:
> FROM permission p
> LEFT JOIN role r WITH p->permission_role_id_fkey = r
> LEFT JOIN team_role tr WITH tr->team_role_role_id_fkey = r
> LEFT JOIN team t WITH tr->team_role_team_id_fkey = t
> LEFT JOIN user_role ur WITH ur->user_role_role_id_fkey = r
> LEFT JOIN "user" u WITH ur->user_role_user_id_fkey = u
> WHERE p.id = 1;
Someone pointed out the part to the right of the last equal sign is redundant.
Also, "KEY" is perhaps a better keyword to use than "WITH",
to indicate it's a join using a foreign KEY.
With these two changes, the query becomes:
FROM permission p
LEFT JOIN role r KEY p->permission_role_id_fkey
LEFT JOIN team_role tr KEY tr->team_role_role_id_fkey
LEFT JOIN team t KEY tr->team_role_team_id_fkey
LEFT JOIN user_role ur KEY ur->user_role_role_id_fkey
LEFT JOIN "user" u KEY ur->user_role_user_id_fkey
WHERE p.id = 1;
/Joel
On Sun, Dec 26, 2021, at 19:33, Sascha Kuhl wrote:
> The Syntax is great. Which language does it come from. I consider it not german. But I understand it mathematically.
> Great extension.
It doesn't come from any language. But I've seen similar features in ORMs, such as the jOOQ Java project. [1]
Actually, I think jOOQ's "ON KEY" terminology might be something to take inspiration from.
In jOOQ, it's a Java method .onKey(), but I think it would look nice in SQL too:
LEFT JOIN role r ON KEY p.permission_role_id_fkey
I think it would be nice if we could simply using dot "." instead of "->" or whatever.
I think it should be possible since "ON KEY" would avoid any ambiguity in how to interpret what comes after.
We would know "permission_role_id_fkey" is a foreign key name and not a column.
Or is the grammar too sensitive for such creativity?
/Joel
Could you make
JOIN key ?
Joel Jacobson <joel@compiler.org> schrieb am So., 26. Dez. 2021, 19:52:
On Sun, Dec 26, 2021, at 19:33, Sascha Kuhl wrote:> The Syntax is great. Which language does it come from. I consider it not german. But I understand it mathematically.> Great extension.It doesn't come from any language. But I've seen similar features in ORMs, such as the jOOQ Java project. [1]Actually, I think jOOQ's "ON KEY" terminology might be something to take inspiration from.In jOOQ, it's a Java method .onKey(), but I think it would look nice in SQL too:LEFT JOIN role r ON KEY p.permission_role_id_fkeyI think it would be nice if we could simply using dot "." instead of "->" or whatever.I think it should be possible since "ON KEY" would avoid any ambiguity in how to interpret what comes after.We would know "permission_role_id_fkey" is a foreign key name and not a column.Or is the grammar too sensitive for such creativity?/Joel
On Sun, Dec 26, 2021, at 19:54, Sascha Kuhl wrote:
> Could you make
>
> JOIN key ?
Not sure what you mean.
Perhaps you can explain by rewriting the normal query below according to your idea?
SELECT *
FROM permission p
LEFT JOIN role r ON p.role_id = r.id
Given the foreign key on "permission" that references "role" is named "permission_role_id_fkey".
/Joel
On Sun, 26 Dec 2021 at 01:47, Joel Jacobson <joel@compiler.org> wrote:
On Sat, Dec 25, 2021, at 21:55, Joel Jacobson wrote:> FROM permission p> LEFT JOIN role r WITH p->permission_role_id_fkey = r> LEFT JOIN team_role tr WITH tr->team_role_role_id_fkey = r> LEFT JOIN team t WITH tr->team_role_team_id_fkey = t> LEFT JOIN user_role ur WITH ur->user_role_role_id_fkey = r> LEFT JOIN "user" u WITH ur->user_role_user_id_fkey = u> WHERE p.id = 1;
Is it going too far to omit the table name? I mean, any given foreign key can only point to one other table:
[....]
LEFT JOIN FOREIGN KEY p->permission_role_id_fkey
LEFT JOIN FOREIGN KEY tr->team_role_role_id_fkey
LEFT JOIN FOREIGN KEY tr->team_role_team_id_fkey
LEFT JOIN FOREIGN KEY ur->user_role_role_id_fkey
LEFT JOIN FOREIGN KEY ur->user_role_user_id_fkey
[....]
or some such; you can determine which other table is involved from the foreign key.
Parenthetically, I'm going to mention I really wish you could us ON and USING in the same join. USING (x, y, z) basically means the same as ON ((l.x, l.y, l.z) = (r.x, r.y, r.z)); so it's clear what putting them together should mean: just take the fields listed in the USING and add them to the ON clause in the same way as is currently done, but allow it even if there is also an explicit ON clause.
On Sun, Dec 26, 2021, at 20:02, Isaac Morland wrote:
> Is it going too far to omit the table name? I mean, any given foreign key can only point to one other table:
That actually how I envisioned this feature to work way back, but it doesn't work, and I'll try to explain why.
As demonstrated, we can omit the referenced_table_alias, as it must either be the table we are currently joining, or is the table that the foreign key references.
But we are not always following foreign keys on tables we have already joined in.
Sometimes, we need to do the opposite, to follow a foreign key on a table we have not yet joined in, and the referenced table is instead a table we have already joined in.
Let's look at each row your example and see if we can work it out.
I've added the "FROM permission p" and also "AS [table alias]",
otherwise the aliases you use won't exist.
> FROM permission p
This row is obviously OK. We now have "p" in scope as an alias for "permission".
> LEFT JOIN FOREIGN KEY p->permission_role_id_fkey AS r
This row would follow the FK on "p" and join the "role" table using the "permission.role_id" column. OK.
> LEFT JOIN FOREIGN KEY tr->team_role_role_id_fkey AS tr
This is where we fail. There is no "tr" table alias yet! So we cannot follow the FK.
The reason why it doesn't work is because the FK is:
FOREIGN KEY team_role (role_id) REFERENCES role
That is, the FK is on the new table we are currently joining in.
On the previous row, we followed a FK on "p" which was a table we had already joined in.
I hope this explains the problem.
/Joel
On Sun, 26 Dec 2021 at 14:37, Joel Jacobson <joel@compiler.org> wrote:
Let's look at each row your example and see if we can work it out.I've added the "FROM permission p" and also "AS [table alias]",otherwise the aliases you use won't exist.> FROM permission pThis row is obviously OK. We now have "p" in scope as an alias for "permission".> LEFT JOIN FOREIGN KEY p->permission_role_id_fkey AS rThis row would follow the FK on "p" and join the "role" table using the "permission.role_id" column. OK.> LEFT JOIN FOREIGN KEY tr->team_role_role_id_fkey AS trThis is where we fail. There is no "tr" table alias yet! So we cannot follow the FK.The reason why it doesn't work is because the FK is:FOREIGN KEY team_role (role_id) REFERENCES roleThat is, the FK is on the new table we are currently joining in.
Right, sorry, that was sloppy of me. I should have noticed that I wrote "tr-> ... AS tr". But in the case where the "source" (referencing) table is already in the join, what's wrong with allowing my suggestion? We do need another way of joining to a new table using one of its foreign keys rather than a foreign key on a table already in the join, but it seems the first case is pretty common.
On Sun, Dec 26, 2021, at 19:52, Joel Jacobson wrote:
> LEFT JOIN role r ON KEY p.permission_role_id_fkey
Ops! I see this doesn't quite work.
We're missing one single bit of information.
That is, we need to indicate if the foreign key is
a) in the table we're currently joining
or
b) to some existing table we've already joined in
Here comes a new proposal:
join_type from_item ON KEY foreign_key_constraint_name [IN referencing_table_alias | TO referenced_table_alias]
ON KEY foreign_key_constraint_name IN referencing_table_alias
- The foreign key is in a table we've already joined in, as given by referencing_table_alias.
ON KEY foreign_key_constraint_name TO referenced_table_alias
- The foreign key is in the table we're currently joining, and the foreign key references the table as given by referenced_table_alias. It's necessary to specify the alias, because the table referenced by the foreign key might have been joined in multiple times as different aliases, so we need to specify which one to join against.
Example:
FROM permission p
LEFT JOIN role r ON KEY permission_role_id_fkey IN p
LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
LEFT JOIN team t ON KEY team_role_team_id_fkey IN tr
LEFT JOIN user_role ur ON KEY user_role_role_id_fkey TO r
LEFT JOIN "user" u ON KEY user_role_user_id_fkey IN ur
Thoughts?
/Joel
On Sun, Dec 26, 2021, at 21:49, Isaac Morland wrote:
> Right, sorry, that was sloppy of me. I should have noticed that I wrote "tr-> ... AS tr". But in the case where the "source"
> (referencing) table is already in the join, what's wrong with allowing my suggestion? We do need another way of joining to
> a new table using one of its foreign keys rather than a foreign key on a table already in the join, but it seems the first case
> is pretty common.
I like your idea!
It's would be nice to avoid having to explicitly specify the referenced table, when simply following a foreign key on a table already in the join.
Before I read your reply, I sent a new message in this thread, suggesting a ON KEY ... [IN | TO] ... syntax.
I think if we combine the ON KEY ... TO ... part of my idea, with your idea, we have a complete neat solution.
Maybe we can make them a little more similar syntax wise though.
Could you accept "ON KEY" instead of "FOREIGN KEY" for your idea?
And would a simple dot work instead of ->?
We would then get:
FROM permission p
LEFT JOIN ON KEY p.permission_role_id_fkey r
LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
LEFT JOIN ON KEY tr.team_role_team_id_fkey t
LEFT JOIN user_role ur ON KEY user_role_role_id_fkey TO r
LEFT JOIN ON KEY ur.user_role_user_id_fkey u
Simply following a foreign key on a table already in the join:
LEFT JOIN ON KEY p.permission_role_id_fkey r
Here, "p" is already in the join, and we follow the "permission_role_id_fkey" foreign key to "role" which we don't need to specify, but we do specify what alias we want for it, that is "r".
If instead joining to a new table using one of its foreign keys:
LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
Here, we follow the foreign key on team_role named "team_role_role_id_fkey" and indicate we want to join against the table alias "r", which will then be asserted to actually be an instance of the "role" table. We need to specify the table alias, as we might have "role" in the join multiple times already as different aliases.
Thoughts?
On Sun, 26 Dec 2021 at 16:24, Joel Jacobson <joel@compiler.org> wrote:
I think if we combine the ON KEY ... TO ... part of my idea, with your idea, we have a complete neat solution.Maybe we can make them a little more similar syntax wise though.Could you accept "ON KEY" instead of "FOREIGN KEY" for your idea?And would a simple dot work instead of ->?
I’m not fixed on the details; writing FOREIGN KEY just felt natural, and I copied the -> from the earlier messages, but I didn’t really mean to promote those specific syntax elements.
One question to consider: which columns get included in the join and under what names? When we join USING there is just one copy of each column in the USING, not one from each source table. This is one of the nicest features of USING. With this new feature it seems like it might make sense to omit the join fields from the added table; tricky bit is they don't necessarily have the same name as existing fields as must be the case with USING.
On Sun, Dec 26, 2021, at 22:24, Joel Jacobson wrote:
> FROM permission p
> LEFT JOIN ON KEY p.permission_role_id_fkey r
> LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
> LEFT JOIN ON KEY tr.team_role_team_id_fkey t
> LEFT JOIN user_role ur ON KEY user_role_role_id_fkey TO r
> LEFT JOIN ON KEY ur.user_role_user_id_fkey u
I think readability can be improved by giving the foreign keys the same names as the referenced tables:
FROM permission p
LEFT JOIN ON KEY p.role r
LEFT JOIN team_role tr ON KEY role TO r
LEFT JOIN ON KEY tr.team t
LEFT JOIN user_role ur ON KEY role TO r
LEFT JOIN ON KEY ur.user u
Toughts?
/Joel
When you join by id, the join is unique. You can have combinations of fields, with multiple fields. Is it a maximum fields question.
Isaac Morland <isaac.morland@gmail.com> schrieb am So., 26. Dez. 2021, 22:37:
On Sun, 26 Dec 2021 at 16:24, Joel Jacobson <joel@compiler.org> wrote:I think if we combine the ON KEY ... TO ... part of my idea, with your idea, we have a complete neat solution.Maybe we can make them a little more similar syntax wise though.Could you accept "ON KEY" instead of "FOREIGN KEY" for your idea?And would a simple dot work instead of ->?I’m not fixed on the details; writing FOREIGN KEY just felt natural, and I copied the -> from the earlier messages, but I didn’t really mean to promote those specific syntax elements.One question to consider: which columns get included in the join and under what names? When we join USING there is just one copy of each column in the USING, not one from each source table. This is one of the nicest features of USING. With this new feature it seems like it might make sense to omit the join fields from the added table; tricky bit is they don't necessarily have the same name as existing fields as must be the case with USING.
On Sun, Dec 26, 2021, at 22:38, Joel Jacobson wrote:
> FROM permission p
> LEFT JOIN ON KEY p.role r
> LEFT JOIN team_role tr ON KEY role TO r
> LEFT JOIN ON KEY tr.team t
> LEFT JOIN user_role ur ON KEY role TO r
> LEFT JOIN ON KEY ur.user u
Hm, might be problematic to reuse dot operator, I think it would be controversial.
Perhaps this would be more SQL idiomatic:
FROM permission p
LEFT JOIN ON KEY role IN p AS r
LEFT JOIN team_role AS tr ON KEY role TO r
LEFT JOIN ON KEY team IN tr AS t
LEFT JOIN user_role AS ur ON KEY role TO r
LEFT JOIN ON KEY user IN ur AS u
/Joel
Perhaps this would be more SQL idiomatic:FROM permission pLEFT JOIN ON KEY role IN p AS rLEFT JOIN team_role AS tr ON KEY role TO rLEFT JOIN ON KEY team IN tr AS tLEFT JOIN user_role AS ur ON KEY role TO rLEFT JOIN ON KEY user IN ur AS u
My second guess would be:
FROM permission pLEFT JOIN role AS r ON [FOREIGN] KEY [(p.col1 [, p.col2 ...])]
where the key spec is only required when there are multiple foreign keys in permission pointing to role.
But my first guess would be that the standards group won't get around to it.
On Sun, Dec 26, 2021, at 23:25, Corey Huinker wrote:
> My second guess would be:
> FROM permission p
> LEFT JOIN role AS r ON [FOREIGN] KEY [(p.col1 [, p.col2 ...])]
>
> where the key spec is only required when there are multiple foreign keys in permission pointing to role.
>
> But my first guess would be that the standards group won't get around to it.
It's a quite nice idea. It would definitively mean an improvement, compared to today's SQL.
Benefits:
* Ability to assert the join is actually performed on foreign key columns.
* Conciser thanks to not always having to specify all key columns on both sides.
However, I see one problem with leaving out the key columns:
First, there is only one FK in permission pointing to role, and we write a query leaving out the key columns.
Then, another different FK in permission pointing to role is later added, and our old query is suddenly in trouble.
/Joel
On Mon, 27 Dec 2021 at 03:22, Joel Jacobson <joel@compiler.org> wrote:
However, I see one problem with leaving out the key columns:First, there is only one FK in permission pointing to role, and we write a query leaving out the key columns.Then, another different FK in permission pointing to role is later added, and our old query is suddenly in trouble.
I thought the proposal was to give the FK constraint name. However, if the idea now is to allow leaving that out also if there is only one FK, then that's also OK as long as people understand it can break in the same way NATURAL JOIN can break when columns are added later. For that matter, a join mentioning column names can break if the columns are changed. But breakage where the query no longer compiles are better than ones where it suddenly means something very different so overall I wouldn't worry about this too much.
>On Mon, Dec 27, 2021, at 15:48, Isaac Morland wrote:
>I thought the proposal was to give the FK constraint name.
>However, if the idea now is to allow leaving that out also if there
>is only one FK, then that's also OK as long as people understand it can break in the same way NATURAL JOIN can break
>when columns are added later. For that matter, a join mentioning column names can break if the columns are changed. But
>breakage where the query no longer compiles are better than ones where it suddenly means something very different so
>overall I wouldn't worry about this too much.
Yes, my proposal was indeed to give the FK constraint name.
I just commented on Corey's different proposal that instead specified FK columns.
I agree with your reasoning regarding the trade-offs and problems with such a proposal.
I still see more benefits in using the FK constraint name though.
I have made some new progress on the idea since last proposal:
SYNTAX
join_type JOIN KEY referencing_alias.fk_name [ [ AS ] alias ]
join_type table_name [ [ AS ] alias ] KEY fk_name REF referenced_alias
EXAMPLE
FROM permission p
LEFT JOIN KEY p.role r
LEFT JOIN team_role tr KEY role REF r
LEFT JOIN KEY tr.team t
LEFT JOIN user_role ur KEY role REF r
LEFT JOIN KEY ur.user u
WHERE p.id = 1;
Foreign key constraint names have been given the same names as the referenced tables.
Thoughts?
/Joel
Joel Jacobson <joel@compiler.org> schrieb am Mo., 27. Dez. 2021, 16:21:
>On Mon, Dec 27, 2021, at 15:48, Isaac Morland wrote:>I thought the proposal was to give the FK constraint name.>However, if the idea now is to allow leaving that out also if there>is only one FK, then that's also OK as long as people understand it can break in the same way NATURAL JOIN can break>when columns are added later. For that matter, a join mentioning column names can break if the columns are changed. But>breakage where the query no longer compiles are better than ones where it suddenly means something very different so>overall I wouldn't worry about this too much.Yes, my proposal was indeed to give the FK constraint name.I just commented on Corey's different proposal that instead specified FK columns.I agree with your reasoning regarding the trade-offs and problems with such a proposal.I still see more benefits in using the FK constraint name though.I have made some new progress on the idea since last proposal:SYNTAXjoin_type JOIN KEY referencing_alias.fk_name [ [ AS ] alias ]join_type table_name [ [ AS ] alias ] KEY fk_name REF referenced_aliasEXAMPLEFROM permission pLEFT JOIN KEY p.role rLEFT JOIN team_role tr KEY role REF rLEFT JOIN KEY tr.team tLEFT JOIN user_role ur KEY role REF rLEFT JOIN KEY ur.user uWHERE p.id = 1;
Ref = in and to, great
Foreign key constraint names have been given the same names as the referenced tables.Thoughts?/Joel
On Mon, 27 Dec 2021 at 10:20, Joel Jacobson <joel@compiler.org> wrote:
Foreign key constraint names have been given the same names as the referenced tables.
While I agree this could be a simple approach in many real cases for having easy to understand FK constraint names, I wonder if for illustration and explaining the feature if it might work better to use names that are completely unique so that it's crystal clear that the names are constraint names, not table names.
On Mon, Dec 27, 2021, at 17:03, Isaac Morland wrote:
> On Mon, 27 Dec 2021 at 10:20, Joel Jacobson <joel@compiler.org> wrote:
>
> Foreign key constraint names have been given the same names as the referenced tables.
>
> While I agree this could be a simple approach in many real cases for having easy to understand FK constraint names, I
> wonder if for illustration and explaining the feature if it might work better to use names that are completely unique so that
> it's crystal clear that the names are constraint names, not table names.
Good point, I agree. New version below:
FROM permission p
LEFT JOIN KEY p.permission_role_id_fkey r
LEFT JOIN team_role tr KEY team_role_role_id_fkey REF r
LEFT JOIN KEY tr.team_role_team_id_fkey t
LEFT JOIN user_role ur KEY user_role_role_id_fkey REF r
LEFT JOIN KEY ur.user_role_user_id_fkey u
WHERE p.id = 1;
Thoughts?
/Joel
First, there is only one FK in permission pointing to role, and we write a query leaving out the key columns.Then, another different FK in permission pointing to role is later added, and our old query is suddenly in trouble.
We already have that problem with cases where two tables have a common x column:
SELECT x FROM a, b
so this would be on-brand for the standards body. And worst case scenario you're just back to the situation you have now.
Isaac Morland <isaac.morland@gmail.com> writes:
> On Mon, 27 Dec 2021 at 03:22, Joel Jacobson <joel@compiler.org> wrote:
>> However, I see one problem with leaving out the key columns:
>> First, there is only one FK in permission pointing to role, and we write a
>> query leaving out the key columns.
>> Then, another different FK in permission pointing to role is later added,
>> and our old query is suddenly in trouble.
> I thought the proposal was to give the FK constraint name. However, if the
> idea now is to allow leaving that out also if there is only one FK, then
> that's also OK as long as people understand it can break in the same way
> NATURAL JOIN can break when columns are added later.
NATURAL JOIN is widely regarded as a foot-gun that the SQL committee
should never have invented. Why would we want to create another one?
(I suspect that making the constraint name optional would be problematic
for reasons of syntax ambiguity, anyway.)
regards, tom lane
On Mon, Dec 27, 2021, at 19:15, Tom Lane wrote:
> NATURAL JOIN is widely regarded as a foot-gun that the SQL committee
> should never have invented. Why would we want to create another one?
>
> (I suspect that making the constraint name optional would be problematic
> for reasons of syntax ambiguity, anyway.)
I agree. I remember this blog post from 2013 discussing the problems
with both NATURAL but also the problems with USING:
Since my last email in this thread, I've learned KEY is unfortunately not a reserved keyword.
This probably means the proposed "JOIN KEY" would be problematic, since a relation could be named KEY.
Can with think of some other suitable reserved keyword?
How about JOIN WITH?
join_type JOIN WITH fk_table.fk_name [ [ AS ] alias ]
join_type JOIN fk_table [ [ AS ] alias ] WITH fk_name REF pk_table
FROM permission p
LEFT JOIN WITH p.permission_role_id_fkey r
LEFT JOIN team_role tr WITH team_role_role_id_fkey REF r
LEFT JOIN WITH tr.team_role_team_id_fkey t
LEFT JOIN user_role ur WITH user_role_role_id_fkey REF r
LEFT JOIN WITH ur.user_role_user_id_fkey u
WHERE p.id = 1;
/Joel
"Joel Jacobson" <joel@compiler.org> writes:
> Since my last email in this thread, I've learned KEY is unfortunately not a reserved keyword.
> This probably means the proposed "JOIN KEY" would be problematic, since a relation could be named KEY.
> Can with think of some other suitable reserved keyword?
FOREIGN? Or even spell out "JOIN FOREIGN KEY".
> How about JOIN WITH?
Seems largely unrelated.
regards, tom lane
On 12/28/21 8:26 PM, Joel Jacobson wrote: > On Mon, Dec 27, 2021, at 19:15, Tom Lane wrote: >> NATURAL JOIN is widely regarded as a foot-gun that the SQL committee >> should never have invented. Why would we want to create another one? >> >> (I suspect that making the constraint name optional would be problematic >> for reasons of syntax ambiguity, anyway.) > > I agree. I remember this blog post from 2013 discussing the problems > with both NATURAL but also the problems with USING: > http://www.databasesoup.com/2013/08/fancy-sql-monday-on-vs-natural-join-vs.html > > Since my last email in this thread, I've learned KEY is unfortunately not a reserved keyword. > This probably means the proposed "JOIN KEY" would be problematic, since a relation could be named KEY. > > Can with think of some other suitable reserved keyword? I don't particularly like this whole idea anyway, but if we're going to have it, I would suggest JOIN ... USING KEY ... since USING currently requires a parenthesized list, that shouldn't create any ambiguity. > How about JOIN WITH? WITH is severely overloaded already. -- Vik Fearing
> How about JOIN WITH?
I'm -1 on this, reusing WITH is just likely to cause confusion because WITH can appear other places in a query having an entirely different meaning. I'd just avoid that from the start.
>> Can with think of some other suitable reserved keyword?
>FOREIGN? Or even spell out "JOIN FOREIGN KEY".
I like the conciseness of just FOREIGN.
On 2021-Dec-27, Joel Jacobson wrote: > >On Mon, Dec 27, 2021, at 15:48, Isaac Morland wrote: > >I thought the proposal was to give the FK constraint name. > >However, if the idea now is to allow leaving that out also if there > >is only one FK, then that's also OK as long as people understand it can break in the same way NATURAL JOIN can break > >when columns are added later. For that matter, a join mentioning column names can break if the columns are changed. But > >breakage where the query no longer compiles are better than ones where it suddenly means something very different so > >overall I wouldn't worry about this too much. > > Yes, my proposal was indeed to give the FK constraint name. > I just commented on Corey's different proposal that instead specified FK columns. By way of precedent we have the ON CONFLICT clause, for which you can specify a constraint name or a list of columns. -- Álvaro Herrera 39°49'30"S 73°17'W — https://www.EnterpriseDB.com/ "Los trabajadores menos efectivos son sistematicamente llevados al lugar donde pueden hacer el menor daño posible: gerencia." (El principio Dilbert)
Vik Fearing <vik@postgresfriends.org> writes:
> On 12/28/21 8:26 PM, Joel Jacobson wrote:
>> Can with think of some other suitable reserved keyword?
> I don't particularly like this whole idea anyway, but if we're going to
> have it, I would suggest
> JOIN ... USING KEY ...
That would read well, which is nice, but I wonder if it wouldn't induce
confusion. You'd have to explain that it didn't work like standard
USING in the sense of merging the join-key columns.
... unless, of course, we wanted to make it do so. Would that
be sane? Which name (referenced or referencing column) would
the merged column have?
regards, tom lane
On 28.12.21 20:45, Vik Fearing wrote: > I don't particularly like this whole idea anyway, but if we're going to > have it, I would suggest > > JOIN ... USING KEY ... > > since USING currently requires a parenthesized list, that shouldn't > create any ambiguity. In the 1990s, there were some SQL drafts that included syntax like JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT ... AFAICT, these ideas just faded away because of other priorities, so if someone wants to revive it, some work already exists.
On 12/28/21 15:10, Tom Lane wrote: > Vik Fearing <vik@postgresfriends.org> writes: >> On 12/28/21 8:26 PM, Joel Jacobson wrote: >>> Can with think of some other suitable reserved keyword? >> I don't particularly like this whole idea anyway, but if we're going to >> have it, I would suggest >> JOIN ... USING KEY ... > That would read well, which is nice, but I wonder if it wouldn't induce > confusion. You'd have to explain that it didn't work like standard > USING in the sense of merging the join-key columns. > > ... unless, of course, we wanted to make it do so. Would that > be sane? Which name (referenced or referencing column) would > the merged column have? > > I agree this would cause confusion. I think your earlier suggestion of JOIN ... FOREIGN KEY ... seems reasonable. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> In the 1990s, there were some SQL drafts that included syntax like
> JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT ...
> AFAICT, these ideas just faded away because of other priorities, so if
> someone wants to revive it, some work already exists.
Interesting! One thing that bothered me about this whole line of
discussion is that we could get blindsided in future by the SQL
committee standardizing the same idea with slightly different
syntax/semantics. I think borrowing this draft text would greatly
improve the odds of that not happening. Do you have access to
full details?
regards, tom lane
On Wed, Dec 29, 2021, at 16:28, Tom Lane wrote:
>Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
>> In the 1990s, there were some SQL drafts that included syntax like
>> JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT ...
>> AFAICT, these ideas just faded away because of other priorities, so if
>> someone wants to revive it, some work already exists.
>
> Interesting! One thing that bothered me about this whole line of
> discussion is that we could get blindsided in future by the SQL
> committee standardizing the same idea with slightly different
> syntax/semantics. I think borrowing this draft text would greatly
> improve the odds of that not happening. Do you have access to
> full details?
Wisely said, I agree.
I have access to the ISO online document database, but the oldest SQL documents I could find there are from 2008-10-15.
I searched for document titles containing "SQL" in both ISO/IEC JTC 1/SC 32 and ISO/IEC JTC 1/SC 32/WG 3.
It would be very interesting to read these old SQL drafts from the 1990s, if they can be found.
/Joel
On Wed, Dec 29, 2021, at 16:28, Tom Lane wrote:
>Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
>> In the 1990s, there were some SQL drafts that included syntax like
>> JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT ...
>> AFAICT, these ideas just faded away because of other priorities, so if
>> someone wants to revive it, some work already exists.
>
> Interesting! One thing that bothered me about this whole line of
> discussion is that we could get blindsided in future by the SQL
> committee standardizing the same idea with slightly different
> syntax/semantics. I think borrowing this draft text would greatly
> improve the odds of that not happening. Do you have access to
> full details?
I read an interesting comment where someone claimed the SQL standard would never
allow using constraint names in a DQL statements. [1]
I responded there already good examples of this in some vendors,
such as PostgreSQL's INSERT INTO ... ON CONFLICT.
(Thanks Alvaro, your reply previously in this thread reminded me of this case.)
I later learned the DQL sublanguage apparently doesn't include INSERT,
but nonetheless I still think it's a good example on the potential value
of using constraint names in queries.
Does anyone know if there is any such general clause in the SQL standard,
that would forbid using constraint names in SELECT queries?
/Joel
On Wed, Dec 29, 2021, at 10:46, Peter Eisentraut wrote:
>In the 1990s, there were some SQL drafts that included syntax like
Do you remember if it was in the beginning/middle/end of the 1990s?
I will start the work of going through all drafts tomorrow.
/Joel