Join tables on columns of composite foreign / primary key in a query

Question

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:

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.

Jesher

Answer 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.

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. The cases where NATURAL JOIN is a good idea are few and far between.

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

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.

Guillema

Would there be any particular advantage to creating a custom type for this scenario? – Beauregard

@IamIC: Not sure where you are going with that. You might start a new question with details. You can always link to this one for context - and optionally leave a comment here. – Guillema

This is a great answer because it shows how you can do it but also explicitly calls out that, while it might work for a given setup, it isn't because of any automatic FK magic – Plotinus

this is a great answer, but my 2 cents ... please don't use Natural Join and be a little more explicit with Using ... it will go a long way to make the code much more maintainable.. – Sawyer

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

Ambassador

Yes, I was looking for a way to write this so that the query is less verbose. I was under the impression that this could be done – Jesher