[解決済み] Postgres ENUMデータ型またはCHECK CONSTRAINT？

質問

MySQL DB を Pg (9.1) に移行しており、Pg で新しいデータ型を作成し、それを列の定義として使用することによって MySQL ENUM データ型をエミュレートしています。質問ですが、CHECK CONSTRAINT を代わりに使用することはできますか、また、その方が良いですか？MySQL の ENUM 型は、行に特定の値のエントリを強制するために実装されています。そして、もしそうなら、それはより良い（または悪い）のでしょうか？

どのように解決するのですか？

ここでのコメントと回答、および初歩的な調査に基づいて、私は以下の要約を提供し、Postgres-eratiからのコメントを求めます。皆様のご意見をお聞かせください。

Postgresデータベースのテーブルカラムのエントリを制限する方法は3つあります。例えば、quot;colors" を格納するテーブルで、'red'、'green'、'blue' のみを有効な項目とする場合を考えてみましょう。

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

   1つは、quot;what you see is what you get,"つまり、テーブル定義に列の有効値がそのまま記録されていること、2つは、すべてのネイティブな文字列および数値演算子が機能することです。

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 を目的のテキスト値に変換します。