Foreign Keys are great!<p>Too bad they were not invented until SQL-89, and could thus not be considered when the JOIN syntax we all know was designed and formalized by ANSI in SQL-86.<p>Imagine if we could simply give the name of a foreign key,
as an argument to JOIN, letting the query planner look up the foreign key columns, to do the join on.<p>The default names for foreign keys are not very user-friendly, due to the SQL standard unfortunate decision to require the foreign key name to be unique per namespace, and not just per table, which would have made more sense.<p>In PostgreSQL, foreign keys just need to be unique per table, so there we could give them names such as "customer" instead of "rental_customer_id_fkey".<p>Imagine if we instead of:<p><pre><code> SELECT
language.name,
COUNT(*)
FROM rental
JOIN customer
ON customer.customer_id = rental.customer_id
JOIN address
ON address.address_id = customer.address_id
JOIN city
ON city.city_id = address.city_id
JOIN country
ON country.country_id = city.country_id
JOIN inventory
ON inventory.inventory_id = rental.inventory_id
JOIN film
ON film.film_id = inventory.film_id
JOIN language
ON language.language_id = film.language_id
WHERE country.country = 'Sweden'
GROUP BY language.name
</code></pre>
Would write something like:<p><pre><code> SELECT
language.name,
COUNT(*)
FROM rental
JOIN rental->rental_customer_id_fkey AS customer
JOIN customer->customer_address_id_fkey AS address
JOIN address->address_city_id_fkey AS city
JOIN city->city_country_id_fkey AS country
JOIN rental->rental_inventory_id_fkey AS inventory
JOIN inventory->inventory_film_id_fkey AS film
JOIN film->film_language_id_fkey AS language
WHERE country.country = 'Sweden'
GROUP BY language.name
</code></pre>
After renaming the foreign keys:<p><pre><code> ALTER TABLE rental RENAME CONSTRAINT rental_customer_id_fkey TO customer;
ALTER TABLE rental RENAME CONSTRAINT rental_inventory_id_fkey TO inventory;
ALTER TABLE customer RENAME CONSTRAINT customer_address_id_fkey TO address;
ALTER TABLE address RENAME CONSTRAINT address_city_id_fkey TO city;
ALTER TABLE city RENAME CONSTRAINT city_country_id_fkey TO country;
ALTER TABLE inventory RENAME CONSTRAINT inventory_film_id_fkey TO film;
ALTER TABLE film RENAME CONSTRAINT film_language_id_fkey TO language;
</code></pre>
We could write this as:<p><pre><code> SELECT
language.name,
COUNT(*)
FROM rental
JOIN rental->customer
JOIN customer->address
JOIN address->city
JOIN city->country
JOIN rental->inventory
JOIN inventory->film
JOIN film->language
WHERE country.country = 'Sweden'
GROUP BY language.name
</code></pre>
And if allowing such a "foreign key operator" to be chained,
we could write:<p><pre><code> SELECT
language.name,
COUNT(*)
FROM rental
JOIN rental->customer->address->city->country
JOIN rental->inventory->film->language
WHERE country.country = 'Sweden'
GROUP BY language.name
</code></pre>
This is similar to "4.9 Reference types" in the SQL standard ISO/IEC 9075-2:2016(E), but it wouldn't require a separate REF column, it would merely use the existing foreign keys which we already have in well designed proper database schemas. We would just need to give them better names.<p>Q: How would we name foreign keys if there are two going to the same table?<p>A: Imagine having a "users" table with two columns "child_user_id" and "parent_user_id", both referencing "users". The foreign keys on such columns could simply be named "child" and "parent".