postgresql Postgres ENUM 数据类型或检查约束？

Postgres ENUM data type or CHECK CONSTRAINT?

提问by punkish

I have been migrating a MySQL db to Pg (9.1), and have been emulating MySQL ENUM data types by creating a new data type in Pg, and then using that as the column definition. My question -- could I, and would it be better to, use a CHECK CONSTRAINT instead? The MySQL ENUM types are implemented to enforce specific values entries in the rows. Could that be done with a CHECK CONSTRAINT? and, if yes, would it be better (or worse)?

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

回答by punkish

Based on the comments and answers here, and some rudimentary research, I have the following summary to offer for comments from the Postgres-erati. Will really appreciate your input.

基于这里的评论和答案，以及一些基本的研究，我有以下总结供 Postgres-erati 提供评论。将非常感谢您的意见。

There are three ways to restrict entries in a Postgres database table column. Consider a table to store "colors" where you want only 'red', 'green', or 'blue' to be valid entries.

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

1. Enumerated data type

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

   Advantages are that the type can be defined once and then reused in as many tables as needed. A standard query can list all the values for an ENUM type, and can be used to make application form widgets.

   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
   

   Disadvantages are, the ENUM type is stored in system catalogs, so a query as above is required to view its definition. These values are not apparent when viewing the table definition. And, since an ENUM type is actually a data type separate from the built in NUMERIC and TEXT data types, the regular numeric and string operators and functions don't work on it. So, one can't do a query like

   SELECT FROM t WHERE color LIKE 'bl%'; 
   

2. Check constraints

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

   Two advantage are that, one, "what you see is what you get," that is, the valid values for the column are recorded right in the table definition, and two, all native string or numeric operators work.

3. Foreign keys

   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)
   );
   

   Essentially the same as creating an ENUM type, except, the native numeric or string operators work, and one doesn't have to query system catalogs to discover the valid values. A join is required to link the color_idto the desired text value.

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'))
   );
   

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

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 类型相同，不同之处在于本机数字或字符串运算符可以工作，并且不必查询系统目录来发现有效值。需要连接才能将 链接color_id到所需的文本值。

回答by Gord Stephen

As other answers point out, check constraints have flexibility issues, but setting a foreign key on an integer id requires joining during lookups. Why not just use the allowed values as natural keys in the reference table?

正如其他答案所指出的，检查约束存在灵活性问题，但在整数 id 上设置外键需要在查找期间加入。为什么不直接使用允许的值作为引用表中的自然键？

To adapt the schema from punkish's answer:

从朋克的回答中调整模式：

CREATE TABLE valid_colors (
    color TEXT PRIMARY KEY
);

INSERT INTO valid_colors (color) VALUES 
    ('red'),
    ('green'),
    ('blue');

CREATE TABLE t (
    color TEXT REFERENCES valid_colors (color) ON UPDATE CASCADE
);

Values are stored inline as in the check constraint case, so there are no joins, but new valid value options can be easily added and existing values instances can be updated via ON UPDATE CASCADE(e.g. if it's decided "red" should actually be "Red", update valid_colorsaccordingly and the change propagates automatically).

值在检查约束情况下内联存储，因此没有连接，但可以轻松添加新的有效值选项，并且可以通过更新现有值实例ON UPDATE CASCADE（例如，如果确定“红色”实际上应该是“红色”，请更新valid_colors相应地，更改会自动传播）。

回答by Frank Heikens

PostgreSQL has enum types, works as it should. I don't know if an enum is "better" than a constraint, they just both work.

PostgreSQL 有enum types，可以正常工作。我不知道枚举是否比约束“更好”，它们都有效。

回答by tomo7

One of the big disadvantages of Foreign keys vs Check constraints is that any reporting or UI displays will have to perform a join to resolve the id to the text.

外键与检查约束的一大缺点是任何报告或 UI 显示都必须执行连接以将 id 解析为文本。

In a small system this is not a big deal but if you are working on a HR or similar system with very many small lookup tables then this can be a very big deal with lots of joins taking place just to get the text.

在一个小型系统中，这不是什么大问题，但是如果您正在使用具有很多小型查找表的 HR 或类似系统，那么这可能是一个非常大的问题，因为为了获取文本而进行了大量连接。

My recommendation would be that if the values are few and rarely changing, then use a constraint on a text field otherwise use a lookup table against an integer id field.

我的建议是，如果值很少且很少更改，则对文本字段使用约束，否则使用针对整数 id 字段的查找表。

回答by Teocali

From my point of view, given a same set of values

从我的角度来看，给定一组相同的值

1. Enum is a better solution if you will use it on multiple column
2. If you want to limit the values of only one column in your application, a check constraint is a better solution.

1. 如果您将在多列上使用 Enum 是更好的解决方案
2. 如果您只想限制应用程序中一列的值，检查约束是更好的解决方案。

Of course, there is a whole lot of other parameters which could creep in your decision process (typically, the fact that builtin operators are not available), but I think these two are the most prevalent ones.

当然，还有很多其他参数可能会影响您的决策过程（通常，内置运算符不可用的事实），但我认为这两个是最普遍的。

回答by quux00

I'm hoping somebody will chime in with a good answer from the PostgreSQL database side as to why one might be preferable to the other.

我希望有人能从 PostgreSQL 数据库方面给出一个很好的答案，说明为什么一个可能比另一个更可取。

From a software developer point of view, I have a slight preference for using check constraints, since PostgreSQL enum's require a cast in your SQL to do an update/insert, such as:

从软件开发人员的角度来看，我稍微倾向于使用检查约束，因为 PostgreSQL 枚举需要在您的 SQL 中进行强制转换来执行更新/插入，例如：

INSERT INTO table1 (colA, colB) VALUES('foo', 'bar'::myenum)

where "myenum" is the enum type you specified in PostgreSQL.

其中“myenum”是您在 PostgreSQL 中指定的枚举类型。

This certainly makes the SQL non-portable (which may not be a big deal for most people), but also is just yet another thing you have to deal with while developing applications, so I prefer having VARCHARs (or other typical primitives) with check constraints.

这当然使 SQL 不可移植（这对大多数人来说可能不是什么大问题），但也是开发应用程序时必须处理的另一件事，所以我更喜欢使用 VARCHAR（或其他典型原语）进行检查约束。

As a side note, I've noticed that MySQL enums do not require this type of cast, so this is something particular to PostgreSQL in my experience.

作为旁注，我注意到 MySQL 枚举不需要这种类型的转换，所以根据我的经验，这是 PostgreSQL 特有的东西。