基于行内其他列限制表中部分列访问的最优实现方案问询

场景背景 #

我有一张transport表，字段包括type、model_name、size、number_of_wheels、fuel、maximum_passenger_count，用来存储不同类型的交通工具数据。现在需要根据type的值控制部分字段的可填写权限：

• 当type是cycle时，不能填写fuel字段
• 当type是sledge时，number_of_wheels和fuel都不能填
• UI要做网格列表，支持直接编辑和新增行，新增时得先选type，再填详情，禁用的字段不能输入

现有方案的问题 #

之前试过几种方案，都有明显缺陷：

• 只在UI层控制：防不住有人直接用数据库脚本或API插非法数据，而且规则改起来要动前端代码，麻烦
• API层校验：还是拦不住直接操作数据库的情况，而且用户提交后才报错，体验差，规则修改同样要改API代码
• 数据库触发器：会把非法值强行设成NULL，用户根本不知道为啥数据没了，排查问题头疼
• 生成列：满足不了同一个字段对不同type有时要填、有时不能填的需求
• 视图隐藏冗余数据：用户会疑惑为啥有些数据突然消失了，没个解释，体验不好
• 加权限配置表：开发量太大，要是多张表都要这套逻辑，重复干活太浪费

最优解决方案 #

结合数据库强校验+UI/API联动+规则集中管理，既保障数据安全，又兼顾用户体验，还方便维护：

1. 数据库层：用CHECK约束+自定义函数做强制校验 #

先写一个校验函数，根据type判断字段是否符合规则，然后给表加CHECK约束，从根源上拦截非法操作：

-- PostgreSQL示例，其他数据库语法稍作调整
CREATE OR REPLACE FUNCTION validate_transport_fields()
RETURNS BOOLEAN AS $$
BEGIN
  CASE type
    WHEN 'cycle' THEN
      -- cycle的fuel必须是空值
      RETURN fuel IS NULL;
    WHEN 'sledge' THEN
      -- sledge的number_of_wheels和fuel都得是空值
      RETURN number_of_wheels IS NULL AND fuel IS NULL;
    -- 其他类型不限制，直接通过
    ELSE RETURN TRUE;
  END CASE;
END;
$$ LANGUAGE plpgsql;

-- 给transport表绑定约束
ALTER TABLE transport ADD CONSTRAINT chk_transport_field_rules
CHECK (validate_transport_fields());

这样不管是UI、API还是直接跑SQL脚本，只要违反规则就会被数据库直接拒绝，还会返回明确的约束错误提示，数据合规性有保障。要改规则的话，只需要更新这个函数就行，不用动表结构。

2. UI/API层：联动规则提升体验 #

• UI端：用户选完type后，立刻把对应的禁用字段灰掉，不让用户输入，提交前再做一次校验，提前给用户友好提示（比如“自行车不需要填燃料类型”）
• API端：同步数据库的规则逻辑，在接收数据时先校验，返回清晰的错误信息，别等数据库报错再反馈，减少用户无效操作

3. 规则集中管理：降低维护成本 #

如果规则经常变，或者要用到多张表上，可以把规则存在一个配置表里，这样改规则只需要改表数据，不用动代码：

-- 创建规则表
CREATE TABLE transport_field_rules (
  type VARCHAR(50) PRIMARY KEY,
  forbidden_fields TEXT[] -- 存禁止填写的字段名，比如['fuel']
);

-- 插入现有规则
INSERT INTO transport_field_rules VALUES ('cycle', ARRAY['fuel']);
INSERT INTO transport_field_rules VALUES ('sledge', ARRAY['number_of_wheels', 'fuel']);

然后修改校验函数，从规则表里读配置：

CREATE OR REPLACE FUNCTION validate_transport_fields()
RETURNS BOOLEAN AS $$
DECLARE
  forbidden_fields TEXT[];
  is_set BOOLEAN;
BEGIN
  -- 读取当前type对应的禁用字段
  SELECT forbidden_fields INTO forbidden_fields 
  FROM transport_field_rules 
  WHERE type = $1.type;

  -- 没有匹配的type，默认允许所有字段
  IF forbidden_fields IS NULL THEN RETURN TRUE; END IF;

  -- 检查每个禁用字段是否为空
  FOREACH field IN ARRAY forbidden_fields LOOP
    EXECUTE format('SELECT $1.%I IS NOT NULL', field) INTO is_set USING $1;
    IF is_set THEN RETURN FALSE; END IF;
  END LOOP;

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

以后要加新的type规则，直接往transport_field_rules表里插数据就行，函数和代码都不用改，多张表的话，复制这个规则表和函数的逻辑就行，扩展性很强。

方案优势 #

• 数据安全：数据库层面的约束彻底堵死非法数据的入口，不管是啥操作都拦得住
• 用户友好：UI提前禁用字段，API及时给提示，用户不用等到数据库报错才知道问题
• 维护简单：规则集中管理，改规则只需要更新配置，不用动多处代码
• 扩展性强：轻松复制到其他类似表，重复开发成本低

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