PostgreSQL跨表共享主键是否可行？实现及DBML文档方案

跨表共享主键是否为最佳实践 #

在你的场景中（三张表严格一对一绑定、行数完全一致），共享主键是合理的设计选择。这种设计本质是将一个逻辑上的大实体拆分为三个独立的物理表，既避免了单表字段过于臃肿，又能通过共享主键保证数据的关联一致性。

但要注意：这种设计仅适用于三者永远保持一对一关系的场景。如果未来业务可能出现一对多的关联（比如一个buy对应多个sell），这种设计会严重限制扩展性，此时需要调整为传统的外键关联模式。

PostgreSQL实现方案 #

1. 创建基础表 buys #

作为主键的源头，使用自增身份列保证id的唯一性：

CREATE TABLE buys (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- 替换为你的业务字段
    buy_amount NUMERIC(12,2) NOT NULL,
    buy_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. 创建 sells 表，绑定 buys 的主键 #

将sells的主键同时设为外键关联buys.id，强制两者id一一对应：

CREATE TABLE sells (
    id INT PRIMARY KEY REFERENCES buys(id),
    -- 替换为你的业务字段
    sell_amount NUMERIC(12,2) NOT NULL,
    sell_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

3. 创建 transaction_pairs 表，共享同一主键 #

直接用id作为主键，同时关联buys和sells，消除冗余的buy_id/sell_id重复存储：

CREATE TABLE transaction_pairs (
    id INT PRIMARY KEY REFERENCES buys(id),
    -- 同时关联sells，保证id一致性
    FOREIGN KEY (id) REFERENCES sells(id),
    -- 替换为你的业务字段
    transaction_fee NUMERIC(12,2) NOT NULL DEFAULT 0.00
);

DBML记录该关系 #

用DBML可以清晰定义这种一对一共享主键的关联：

Table buys {
  id INT [pk, increment]
  buy_amount NUMERIC(12,2) [not null]
  buy_timestamp TIMESTAMP [not null, default: 'CURRENT_TIMESTAMP']
}

Table sells {
  id INT [pk, ref: > buys.id]
  sell_amount NUMERIC(12,2) [not null]
  sell_timestamp TIMESTAMP [not null, default: 'CURRENT_TIMESTAMP']
}

Table transaction_pairs {
  id INT [pk, ref: > buys.id, ref: > sells.id]
  transaction_fee NUMERIC(12,2) [not null, default: 0.00]
}

进一步减少冗余的方案 #

1. 移除重复字段：删除transaction_pairs中的buy_id和sell_id，仅保留id作为共享主键，通过双外键关联另外两张表，彻底消除id值的重复存储。
2. 评估transaction_pairs的必要性：如果transaction_pairs没有独立的业务字段（仅用于关联），可以直接去掉该表，通过buys.id和sells.id的相等关系实现关联查询，避免额外表的冗余。
3. 简化约束：利用PostgreSQL的外键机制替代显式的CHECK约束，减少冗余的校验逻辑。

内容的提问来源于stack exchange，提问作者Virgilio Gonzenbach