PostgreSQL查询完整性约束及校验规则的实现方法

实现方案 #

你当前用的是Oracle的系统视图查询，要拿到完整的约束信息、校验规则，需要关联user_cons_columns视图获取约束关联的字段，再通过字段拼接得到校验规则说明，完整查询语句如下：

SELECT 
    uc.table_name,
    uc.constraint_name,
    -- 转换约束类型为可读描述
    CASE uc.constraint_type
        WHEN 'P' THEN '主键约束'
        WHEN 'U' THEN '唯一约束'
        WHEN 'C' THEN '检查约束'
        WHEN 'R' THEN '外键约束'
        WHEN 'V' THEN '视图检查选项约束'
        WHEN 'O' THEN '视图只读约束'
        ELSE '其他约束'
    END AS constraint_type,
    -- 生成校验规则详情
    CASE uc.constraint_type
        WHEN 'P' THEN '校验逻辑：表'||uc.table_name||'的字段('||LISTAGG(ucc.column_name, ',') WITHIN GROUP (ORDER BY ucc.position)||')值非空且唯一，作为行唯一标识'
        WHEN 'U' THEN '校验逻辑：表'||uc.table_name||'的字段('||LISTAGG(ucc.column_name, ',') WITHIN GROUP (ORDER BY ucc.position)||')值全局唯一'
        WHEN 'C' THEN '校验逻辑：'||uc.search_condition
        WHEN 'R' THEN '校验逻辑：表'||uc.table_name||'的字段('||LISTAGG(ucc.column_name, ',') WITHIN GROUP (ORDER BY ucc.position)||')的值必须存在于关联表'||uc.r_table_name||'的对应主键字段中'
        ELSE '无明确校验规则'
    END AS constraint_detail
FROM user_constraints uc
LEFT JOIN user_cons_columns ucc 
    ON uc.constraint_name = ucc.constraint_name 
    AND uc.table_name = ucc.table_name
-- 仅查询指定表时保留下面的WHERE条件，查询所有表直接注释即可
-- WHERE uc.table_name = 'MYTABLE'
GROUP BY uc.table_name, uc.constraint_name, uc.constraint_type, uc.search_condition, uc.r_table_name
ORDER BY uc.table_name ASC, uc.constraint_name ASC;

注意事项 #

• Oracle系统视图默认存储的表名、约束名都是大写，如果你建表时用双引号指定了小写名称，需要调整WHERE条件里的表名大小写
• 检查约束的自定义校验规则直接存储在search_condition字段中，可直接读取
• 如果需要查询整个实例下所有用户的约束，将user_constraints替换为dba_constraints、user_cons_columns替换为dba_cons_columns即可，需要对应管理员权限

内容的提问来源于stack exchange，提问作者fenton.raine