Re: Foreign key joins revisited

>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