Postgres ENUM数据类型还是CHECK CONSTRAINT？ [英] Postgres ENUM data type or CHECK CONSTRAINT?

问题描述

我一直在将MySQL数据库迁移到Pg（9.1），并通过在Pg中创建新的数据类型，然后将其用作列定义来模拟MySQL ENUM数据类型。我的问题-我可以使用CHECK CONSTRAINT代替它吗？ MySQL ENUM类型的实现是为了强制在行中输入特定的值。可以用检查约束完成吗？

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

推荐答案

根据此处的评论和答案以及一些基本知识，是否会更好（或更糟）？研究中，我提供以下摘要供Postgres-erati提出意见。

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数据库表列中的条目。考虑一个存储颜色的表，您只希望在其中红色，绿色或蓝色作为有效条目。

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.

1. 枚举数据类型

1. Enumerated data type

CREATE TYPE valid_colors AS ENUM ('red', 'green', 'blue');

CREATE TABLE t (
    color VALID_COLORS
);

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

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

缺点是，ENUM类型存储在系统目录中，因此需要执行上述查询查看其定义。当查看表定义时，这些值不明显。而且，由于ENUM类型实际上是与内置NUMERIC和TEXT数据类型不同的数据类型，因此常规数字和字符串运算符以及函数无法在其上使用。因此，无法执行类似

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

检查约束

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.

外键

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 链接到所需的文本值。

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_id to the desired text value.

这篇关于Postgres ENUM数据类型还是CHECK CONSTRAINT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！