如何在PostgreSQL中创建0-5整数id与描述的映射表？

表结构设计 #

因为映射的整数取值固定为0-5，表B仅需两个字段，同时加入必要约束避免非法数据写入：

• 主键字段map_id：整数类型，作为关联的唯一标识，通过检查约束限定取值必须在0~5区间内
• 描述字段map_desc：文本类型，存储对应整数id的描述信息，设置非空约束保证每个合法id都有对应描述

对应的建表语句：

CREATE TABLE table_b (
    map_id INTEGER PRIMARY KEY,
    map_desc TEXT NOT NULL,
    CONSTRAINT chk_map_id_valid_range CHECK (map_id >= 0 AND map_id <= 5)
);

初始化固定映射数据 #

按照给定的对应关系批量插入初始数据，由于所有映射值固定，无需配置自增序列：

INSERT INTO table_b (map_id, map_desc)
VALUES
    (0, 'id_description_0'),
    (1, 'id_description_1'),
    (2, 'id_description_2'),
    (3, 'id_description_3'),
    (4, 'id_description_4'),
    (5, 'id_description_5');

与现有表A的关联优化 #

如果需要保证表A中存储的整数id完全匹配映射表的合法值，可以为表A的对应id字段添加外键约束，实现参照完整性：

-- 为已存在的表A添加外键关联
ALTER TABLE table_a
ADD CONSTRAINT fk_a_map_reference
FOREIGN KEY (id) REFERENCES table_b(map_id);

注意事项 #

• 由于映射范围完全固定，这套设计没有冗余字段，查询时直接关联表B即可获取描述信息
• 若后续需要扩展整数取值范围，仅需要先调整表B的检查约束范围，再插入新的映射值即可

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