Re: Foreign key joins revisited

On Wed, Dec 29, 2021, at 16:28, Tom Lane wrote:
>Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)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

[1] https://news.ycombinator.com/item?id=29739147#29743102