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);
然而,我的印象是,有一种不那么冗长的方式来实现这一点.