查询中复合外键/主键列上的连接表

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)
)

What is a good way to query for deliveries given a particular subscription? Is there a way to assign a column name to PRIMARY KEY (magazine_id, user_id) and the corresponding foreign key so that I can query like this

对于给定的订阅，查询交付的好方法是什么?是否有一种方法可以将列名分配给主键(magazine_id、user_id)和相应的外键，以便我可以这样查询

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

Note: I can write something like this:

注意:我可以这样写:

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

However, I am under the impression that there is a less verbose way to achieve this.

然而，我的印象是有一种不那么冗长的方法来实现这一点。

2 个解决方案

#1

There is a NATURAL JOIN:

有一个自然的结合:

SELECT *
FROM subscription NATURAL JOIN delivery

Quoting the manual on SELECT:

引用选择手册:

NATURAL

自然

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

NATURAL是一个使用列表的简写，该列表提到两个具有相同名称的表中的所有列。

It would work for your test setup, but it's not strictly doing what you ask for. The connection is based on all columns sharing the same name. Foreign keys are not considered.

它可以为您的测试设置工作，但是它并没有严格地按照您的要求去做。该连接基于共享相同名称的所有列。不考虑外键。

Simplify code / less verbose

For starters, you could use table aliases and you don't need parentheses around the join conditions with ON (unlike with USING):

对于初学者，您可以使用表别名，并且不需要在与ON的连接条件周围加上圆括号(与使用不同):

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

Since column names in the join conditions are identical, you can further simplify with USING:

由于连接条件中的列名是相同的，您可以使用以下方法进一步简化:

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

There is no syntax variant making joins based on foreign key constraints automatically. You would have to query the system catalogs and build the SQL dynamically for that ...

没有基于外键约束自动生成连接的语法变体。您必须查询系统编目并为此动态构建SQL……

#2

Doesn't delivery has two columns representing the foreign key? Then it should work like with a non-composite primary key SELECT * FROM subscription JOIN delivery ON (delivery.magazine_id = subscription.magazine_id AND delivery.user_id = subscription.user_id).

交付不是有两个列表示外键吗?然后，它应该像使用非组合主键SELECT *一样，从订阅连接交付(交付)。magazine_id =订阅。magazine_id和交付。user_id = subscription.user_id)。