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