Объединение таблиц в столбцах составного внешнего/первичного ключа в запросе 

CREATE TABLE subscription (
   magazine_id bigint,
   user_id     bigint,
   PRIMARY KEY (magazine_id, user_id)
);

CREATE TABLE delivery (
   magazine_id bigint,
   user_id     bigint,
   FOREIGN KEY (subscription) REFERENCES subscription (magazine_id, user_id)
);

Как правильно запрашивать доставку по определенной подписке? Есть ли способ присвоить имя столбца PRIMARY KEY (magazine_id, user_id) и соответствующий внешний ключ, чтобы я мог сделать запрос следующим образом:

SELECT *
FROM subscription
JOIN delivery ON (delivery.subscription_fk = delivery.subscription_pk);

Примечание. Я могу написать что-то вроде этого:

SELECT *
FROM subscription
JOIN delivery ON (delivery.magazine_id = subscription.magazine_id
              AND delivery.user_id = subscription.user_id);

Однако у меня сложилось впечатление, что для этого есть менее подробный способ.

Ответы

Ответ 1

Существует NATURAL JOIN:

SELECT *
FROM   subscription
NATURAL JOIN delivery;

Цитируя руководство по SELECT:

NATURAL

NATURAL - сокращение от списка USING, в котором упоминаются все столбцы в двух таблицах с одинаковыми именами.

Он будет работать для вашей тестовой установки, но он не выполняет в точности то, что вы просите. Соединение основано на всех столбцах с одинаковым именем. Внешние ключи не рассматриваются. Случаи, когда NATURAL JOIN является хорошей идеей, немногочисленны.

Упростить код/​​меньше подробностей

Для начала вы можете использовать псевдонимы таблиц, и вам не нужны скобки вокруг условий соединения с ON (в отличие от USING):

SELECT *
FROM   subscription s
JOIN   delivery     d ON d.magazine_id = s.magazine_id
                     AND d.user_id = s.user_id;

Поскольку имена столбцов в условиях объединения идентичны, вы можете еще больше упростить с помощью USING:

SELECT *
FROM   subscription s
JOIN   delivery     d USING (magazine_id, user_id);

Нет синтаксического варианта, позволяющего автоматически объединять соединения на основе ограничений внешнего ключа. Вам придется запрашивать системные каталоги и динамически создавать SQL.

Ответ 2

Не имеет ли delivery два столбца, представляющих внешний ключ? Затем он должен работать как с некомпозиционным первичным ключом SELECT * FROM subscription JOIN delivery ON (delivery.magazine_id = subscription.magazine_id AND delivery.user_id = subscription.user_id).