Postgres ENUM データ型か CHECK CONSTRAINT か? 質問する

Question

ここでのコメントと回答、およびいくつかの基本的な調査に基づいて、Postgres-erati からのコメントに対して次の要約を提供します。皆さんの意見をいただければ幸いです。

Postgres データベーステーブル列のエントリを制限する方法は 3 つあります。「色」を保存するテーブルで、「赤」、「緑」、または「青」のみを有効なエントリにしたいとします。

列挙データ型
```
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%';

チェック制約

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

2 つの利点は、1 つは「見た通りの結果が得られる」、つまり列の有効な値がテーブル定義に直接記録されること、もう 1 つはすべてのネイティブの文字列演算子または数値演算子が機能することです。

外部キー

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目的のテキスト値にリンクするには結合が必要です。

Answer 1