postgresql - Postgres ENUM 数据类型或 CHECK CONSTRAINT？

Question

我一直在将 MySQL 数据库迁移到 Pg (9.1)，并且一直在通过在 Pg 中创建新的数据类型来模拟 MySQL ENUM 数据类型，然后将其用作列定义。我的问题——我可以使用 CHECK CONSTRAINT 代替吗？实现 MySQL ENUM 类型以在行中强制执行特定值条目。这可以用 CHECK CONSTRAINT 来完成吗？如果是，它会更好(或更糟)吗？

Answer 1

根据此处的评论和答案以及一些初步研究，我有以下摘要供 Postgres-erati 评论。非常感谢您的意见。

有三种方法可以限制 Postgres 数据库表列中的条目。考虑一个存储“颜色”的表，您只希望“红色”、“绿色”或“蓝色”是有效条目。

1. 枚举数据类型

   CREATE TYPE valid_colors AS ENUM ('red', 'green', 'blue');
   
   CREATE TABLE t (
       color VALID_COLORS
   );
   

   优点是类型可以定义一次，然后根据需要在尽可能多的表中重复使用。标准查询可以列出 ENUM 类型的所有值，并可用于制作申请表小部件。

   SELECT  n.nspname AS enum_schema,  
           t.typname AS enum_name,  
           e.enumlabel AS enum_value
   FROM    pg_type t JOIN 
           pg_enum e ON t.oid = e.enumtypid JOIN 
           pg_catalog.pg_namespace n ON n.oid = t.typnamespace
   WHERE   t.typname = 'valid_colors'
   
    enum_schema | enum_name     | enum_value 
   -------------+---------------+------------
    public      | valid_colors  | red
    public      | valid_colors  | green
    public      | valid_colors  | blue
   

   缺点是，ENUM 类型存储在系统目录中，因此需要如上查询才能查看其定义。查看表定义时，这些值并不明显。而且，由于 ENUM 类型实际上是一种独立于内置 NUMERIC 和 TEXT 数据类型的数据类型，因此常规数字和字符串运算符和函数对其不起作用。所以，不能像这样查询

   SELECT FROM t WHERE color LIKE 'bl%'; 
   

2. 检查约束

   CREATE TABLE t (
       colors TEXT CHECK (colors IN ('red', 'green', 'blue'))
   );
   

   两个优点是，第一，“所见即所得”，即列的有效值直接记录在表定义中，第二，所有 native 字符串或数字运算符都有效。

3. 外键

   CREATE TABLE valid_colors (
       id SERIAL PRIMARY KEY NOT NULL,
       color TEXT
   );
   
   INSERT INTO valid_colors (color) VALUES 
       ('red'),
       ('green'),
       ('blue');
   
   CREATE TABLE t (
       color_id INTEGER REFERENCES valid_colors (id)
   );
   

   本质上与创建 ENUM 类型相同，除了 native 数字或字符串运算符可以工作，并且不必查询系统目录即可发现有效值。需要连接才能将 color_id 链接到所需的文本值。