PostgreSQLの制約：除外、部分的一意、遅延制約など。

データの整合性は簡単に破られます。 アプリケーションコードのエラーが原因で、価格フィールドの値が0になる場合があります（特定のオンラインストアで商品を$ 0で販売したため、定期的にニュースがポップアップします）。 または、ユーザーがテーブルから削除されたが、彼に関するいくつかのデータが他のテーブルに残っており、このデータが何らかのインターフェースで取得されたことが起こります。



PostgreSQLは、他のDBMSと同様に、データの挿入/変更時にいくつかのチェックを実行できます。これを使用できる必要があります。 確認できるものを見てみましょう。

1. DOMAINキーワードを使用したカスタムサブタイプ

PostgreSQLでは、いくつかのintまたはテキストに基づいて型を作成し、いくつかのことをさらに検証できます。

CREATE DOMAIN us_postal_code AS TEXT CHECK( VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$' );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

タイプus_postal_codeを作成し、それを記述するためのさまざまなオプションを定期的にチェックします。 これで、誰も間違って「バルマレーエフ通り」を書くことができなくなります。インデックスしかありません。

 CREATE TABLE users ( id integer, name text, email text, postal_code us_postal_code ) ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

さらに、フェースレス整数またはテキストとは対照的に、タイプ自体がコードの内容を説明するため、これによりコードの可読性が向上します。

2.チェック（特にjsonbとhstoreのチェックに関連）

上記では、CHECK演算子を使用したus_postal_codeを使用しました。 まったく同じことをCREATE TABLE構造に書くことができます。

 CREATE TABLE users ( id integer, name text, email text, postal_code us_postal_code, CHECK (length(name) >= 1 AND length(name) <= 300) ) ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

または、商品が入ったテーブルに小切手（価格> 0）を入れると、0ルーブルでラップトップを販売できなくなります。 または、ストレージを作成してチェック（superCheckFunction（価格））を使用し、このストレージ内の一連のロジックをチェックできます。



ところで、varchar（100）型はテキスト型と同じですが、長さのチェックが追加されています。

挿入または更新のたびにチェックが行われることを理解する必要があります。したがって、テーブルに1秒あたり100,500レコードがある場合、チェックする価値がない場合があります。



jsonbやhstoreなどの汎用データ型をチェックすることが重要になる場合があります。何でもそこに詰め込むことができるからです。 jsonにいくつかのキーが存在するかどうか、またはその値がそこにあるはずのものと一致するかどうかを確認できます。

3.単純なものと部分的なものの両方の一意性を確認します。

メールはユーザーごとに異なる必要があることを簡単に確認します。

 CREATE TABLE users ( id integer, name text, email text, postal_code us_postal_code, deleted boolean, UNIQUE(email) ) ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ただし、テーブル全体ではなく、たとえば、特定のステータスを持つユーザーの一意性のみを確認する必要がある場合があります。



単純なUNIQUEの代わりに、このような一意のインデックスを追加できます。

 CREATE UNIQUE INDEX users_unique_idx ON users(email) WHERE deleted = false;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その後、アンインストールされたユーザーについてのみ、メールの一意性がチェックされます。 条件を挿入できる場所。



また、2つ以上のフィールドに対して一意のインデックスを一度に作成できることにも注意してください。 ユニークな組み合わせをご覧ください。

4.除外

EXCLUDEステートメントを使用して、別の種類の一意性を作成できます。 実際には、組み込みおよび拡張機能を介して追加された多くのデータ型が進行中です。 たとえば、ip4rデータタイプがあり、1つのフィールドにIPアドレスの範囲を保存できます。



そして、重複しない範囲をテーブルに保存する必要があるとします。 一般に、&&演算子を使用して2つの範囲が交差するかどうかを確認できます。たとえば、SELECT '127.0.0.0/24' && '127.0.0.1/32'はtrueを返します。



最後に、次のことを行います。

 CREATE TABLE ip_ranges ( ip_range ip4r, EXCLUDE USING gist (ip_range WITH &&) );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、挿入/更新時に、postgresは挿入される行と交差するかどうか（つまり、&&演算子を使用してtrueが返されるかどうか）をすべての行で調べます。 要点インデックスのおかげで、このチェックは非常に迅速です。

5. NOT NULL

ここではすべてが明確であり、列をNULLにすることはできません。 多くの場合（必ずしもそうではありませんが）DEFAULTと連動します。



例：

 CREATE TABLE users ( id integer, name text NOT NULL, email text NOT NULL, postal_code us_postal_code, is_married BOOLEAN NOT NULL DEFAULT true, UNIQUE(email) ) ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

nullではない新しい列を既存のテーブルに追加するときは注意してください。 実際、テーブルが非常に大きく、たとえば数千万行であっても、PostgreSQLはnullを許可する通常の列を追加します。 彼はディスク上にあるデータを物理的に変更する必要がないため、postgresのnullはスペースを占有しません。 ただし、null以外のデフォルトの列名テキストを「Vasya」に追加すると、実際には各行を更新するのに役立ちます。これには時間がかかることがあり、状況によっては受け入れられない場合があります。



したがって、このような列は、2つの段階で巨大なテーブルに追加されることがよくあります。 最初に、新しい列のデータをバッチで記入し、それからヌル以外に入れます。

6.主キー、つまり 主キー

これは主キーであるため、一意である必要があり、空にすることはできません。 一般に、PostgreSQLではPRIMARY KEYはUNIQUEとNOT NULLの組み合わせとして機能します。



他のデータベースでは、PRIMARY KEYが他のことを行います。たとえば、MySQL（Innodb）では、このフィールドへのアクセスを高速化するために、PKの周りにデータが自動的にクラスター化されます。 （ちなみに、これはコースで行うこともできますが、手動で、CLUSTERコマンドを使用します。ただし、通常これは必要ありません）

7.外部キー

たとえば、テーブルがあります

 CREATE TABLE items ( id bigint PRIMARY KEY, name varhar(1000), status_id int );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

およびステータス表

 CREATE TABLE status_dictionary ( id int PRIMARY KEY, status_name varchar(100) );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

status_id列がstatus_dictionaryテーブルのIDと一致することをベースに伝えることができます。 たとえば、次のように：

 CREATE TABLE items ( id bigint PRIMARY KEY, name varhar(1000), status_id int REFERENCES status_dictionary(id) );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、status_dictionariesテーブルからnullまたはIdのみをstatus_idに書き込むことができます。



次の2つのフィールドでこれを行うこともできます。

  FOREIGN KEY (a,b) REFERENCES other_table(x,y);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

貼り付けるときは、挿入するたびにDBMSがかなり多くのものをブロックするため、再びオーバーヘッドが発生します。 したがって、（非常に）集中的な挿入では、おそらく外部キーの使用を乱用しないでください。

8. DEFERRABLE

パフォーマンスのために、constrateのチェックを延期する必要がある場合、constrateにキーワードDEFERRABLEを付けることができます。



たとえば、一意（メール）DEFERRABLE INITIALLY DEFERREDにすると、トランザクション内に書き込むことができます。

 SET CONSTRAINTS ALL DEFERRED
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、すべてのチェックが遅延し、実際には、commitという単語の前にのみ発生します

これは、UNIQUE、PRIMARY KEY、およびREFERENCESでは機能しますが、NOT NULLおよびCHECKでは機能しません。