如何在现有表中设置主键？基于给定panama_edge表DDL的主键设置咨询

当前表结构设置主键的限制 #

你当前的表结构无法直接设置主键，存在两个核心问题：

• 所有字段均定义为NULL属性，主键要求参与主键的所有字段必须为NOT NULL，不允许存储空值
• start_id、end_id使用float8浮点类型，浮点类型存在精度误差，等值判断容易出现非预期的结果，完全不适合作为主键或外键的字段类型

start_id与end_id的定位说明 #

从表名和字段来看，该表属于图结构的边表，存储两个节点的关联关系，这两个字段的定位是外键而非主键：

• 二者对应节点表的唯一标识，应该关联节点表的主键，用于校验边两端节点的合法性，保证数据一致性
• 二者不适合作为主键：业务场景中大概率存在两个节点之间有多条不同类型、不同生效时间的边的情况，仅用start_id+end_id的复合字段无法唯一标识一条边，会出现主键重复问题

推荐处理方案 #

1. 主键设置
   优先新增独立主键字段，避免业务字段变动影响主键稳定性，示例修改后的DDL如下：

   CREATE TABLE panama.panama_edge 
   (
       edge_id bigserial PRIMARY KEY, -- 新增自增主键
       start_id int8 NOT NULL, -- 修改为非空、适配节点ID的整型，也可根据节点ID类型调整为varchar等
       "TYPE" text NOT NULL,
       end_id int8 NOT NULL,
       link text NULL,
       start_date date NOT NULL, -- 日期字段从text改为日期类型，适配业务场景也可以用timestamp
       end_date date NULL,
       sourceid text NULL,
       valid_until date NULL
   );
   

   如果你确定业务上start_id+end_id+TYPE+start_date可以唯一标识一条边，也可以将这四个字段设置为复合主键，无需新增独立主键字段。
2. 外键设置
   如果你存在对应的节点表（比如panama.panama_node），且节点表主键为node_id，可以给start_id和end_id增加外键约束：

   ALTER TABLE panama.panama_edge ADD CONSTRAINT fk_edge_start_node FOREIGN KEY (start_id) REFERENCES panama.panama_node(node_id);
   ALTER TABLE panama.panama_edge ADD CONSTRAINT fk_edge_end_node FOREIGN KEY (end_id) REFERENCES panama.panama_node(node_id);
   

3. 额外优化建议
   把所有存储日期的字段从text类型调整为date或timestamp类型，既可以校验日期格式合法性，也方便后续做时间范围查询、日期计算等操作。

内容的提问来源于stack exchange，提问作者Handri Mauludin Maulana