Re: Foreign key joins revisited

Joel Jacobson <joel(at)compiler(dot)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:
>
> 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;
>

Ref = in and to, great

>
> Foreign key constraint names have been given the same names as the
> referenced tables.
>
> Thoughts?
>
> /Joel
>