制約 - PolarDB - Alibaba Cloud ドキュメントセンター

この記事では、制約の関連内容を紹介します。

概要

データ型は、テーブルに格納できるデータの種類を制限する方法です。しかし、多くの用途では、それらが提供する制約は粗すぎる。たとえば、製品価格を含む列は、おそらく正の値のみを受け入れる必要があります。ただし、正の数のみを受け入れる標準データ型はありません。もう1つの問題は、列データを他の列または行に対して制約することです。たとえば、製品情報を含むテーブルでは、製品番号ごとに1つの行だけがあるはずです。

そのために、SQLでは列とテーブルの制約を定義できます。制約により、テーブル内のデータを自由に制御できます。ユーザーが制約に違反するデータを列に格納しようとすると、エラーが発生します。これは、値がデフォルト値定義から来た場合でも適用されます。

制約を確認する

チェック制約は、最も一般的な制約タイプです。特定の列の値がブール (真理値) 式を満たす必要があることを指定できます。たとえば、正の製品価格を要求するには、次のものを使用できます。

テーブル製品を作成する (
        product_no整数、
        テキスト名、
        価格数値チェック (価格> 0)
    );

このように、制約定義は、デフォルト値の定義と同様に、データ型の後に配置されます。デフォルト値と制約は任意の順序でリストできます。チェック制約は、キーワードcheckとそれに続く括弧内の式で構成されます。チェック制約式は、このように制約された列を含むべきであり、そうでなければ制約はあまり意味をなさない。

制約に別の名前を付けることもできます。これにより、エラーメッセージが明確になり、変更が必要なときに制約を参照できます。構文は次のとおりです。

テーブル製品を作成する (
        product_no整数、
        テキスト名、
        価格数値CONSTRAINT positive_price CHECK (価格> 0)
    );

したがって、名前付き制約を指定するには、キーワードconstraintとそれに続く識別子とそれに続く制約定義を使用します。 (このように制約名を指定しない場合、システムは自分の名前を選択します。)

チェック制約は、複数の列を参照することもできます。通常価格と割引価格を保存し、割引価格が通常価格よりも低くなるようにしたいとします。

テーブル製品を作成する (
        product_no整数、
        テキスト名、
        価格数値チェック (価格> 0) 、
        discounted_price numeric CHECK (discounted_price > 0) 、
        CHECK (price > discounted_price)
    );

最初の2つの制約は見慣れているはずです。 3番目は新しい構文を使用します。特定の列にアタッチされるのではなく、コンマで区切られた列リストの別の項目として表示されます。列の定義とこれらの制約の定義は、混合した順序でリストできます。

最初の2つの制約は列制約であり、3番目の制約は1つの列定義とは別に記述されるため、テーブル制約であると言います。列制約はテーブル制約として記述することもできますが、列制約はそれがアタッチされている列のみを参照することになっているため、その逆は必ずしも可能ではありません。 (PostgreSQLではそのルールは適用されませんが、テーブル定義を他のデータベースシステムと連携させる場合は、そのルールに従う必要があります。) 上記の例は、次のように記述することもできます。

テーブル製品を作成する (
        product_no整数、
        テキスト名、
        価格数値、
        チェック (価格> 0) 、
        discounted_price数値、
        CHECK (discounted_price > 0) 、
        CHECK (price > discounted_price)
    );

またはさらに:

テーブル製品を作成する (
        product_no整数、
        テキスト名、
        価格数値チェック (価格> 0) 、
        discounted_price数値、
        チェック (discounted_price > 0 AND price > discounted_price)
    );

テーブル制約には、列制約と同じ方法で名前を割り当てることができます。

テーブル製品を作成する (
        product_no整数、
        テキスト名、
        価格数値、
        チェック (価格> 0) 、
        discounted_price数値、
        CHECK (discounted_price > 0) 、
        CONSTRAINT valid_discount CHECK (price > discounted_price)
    );

説明

チェック式が真またはヌル値に評価される場合、チェック制約が満たされることに留意されたい。オペランドがnullの場合、ほとんどの式はnull値を評価するため、制限された列のnull値を防ぐことはできません。列にnull値が含まれないようにするには、次のセクションで説明するnullでない制約を使用できます。

注意

PostgreSQLは、チェック対象の新しい行または更新された行以外のテーブルデータを参照するCHECK制約をサポートしていません。このルールに違反するCHECK制約は単純なテストでは機能するように見えるかもしれませんが、データベースが制約条件が偽である状態に到達しないことを保証することはできません (関係する他の行のその後の変更のため) 。これにより、データベースのダンプと復元が失敗します。完全なデータベースの状態が制約と一致している場合でも、制約を満たす順序で行が読み込まれていないため、復元が失敗する可能性があります。可能であれば、UNIQUE、EXCLUDE、またはFOREIGN KEY制約を使用して、クロス行およびクロステーブルの制限を表現します。

継続的に維持される一貫性の保証ではなく、行挿入時に他の行に対する1回限りのチェックが必要な場合は、カスタムトリガーを使用してそれを実装できます。 (このアプローチでは、データの復元後までpg_dumpがトリガーを再インストールしないため、dump/restoreの問題が回避され、dump/restoreの間にチェックが適用されません。)

PostgreSQLは、CHECK制約条件は不変であると想定しています。つまり、同じ入力行に対して常に同じ結果が得られます。この仮定は、行が挿入または更新されたときにのみCHECK制約を調べることを正当化するものです。 (他のテーブルデータを参照しないことに関する上記の警告は、実際にはこの制限の特別なケースです。)

この仮定を破る一般的な方法の例は、CHECK式のユーザー定義関数を参照してから、その関数の動作を変更することです。PostgreSQLはそれを許可しませんが、テーブルにCHECK制約に違反する行があるかどうかはわかりません。これにより、後続のデータベースダンプと復元が失敗します。このような変更を処理する推奨される方法は、制約を削除し (ALTER TABLEを使用) 、関数定義を調整し、制約を再追加して、すべてのテーブル行に対して再チェックすることです。

ヌルではない制約

not-null制約は、単に、列がnull値を仮定してはならないことを指定します。構文例:

テーブル製品を作成する (
        product_no整数NOT NULL,
        nameテキストNOT NULL,
        価格数値
    );

nullでない制約は、常に列制約として記述されます。 nullでない制約は、チェック制約check (is not NULL) を作成することと機能的に同等ですが、PostgreSQLでは明示的なnullでない制約を作成する方が効率的です。欠点は、この方法で作成されたnot-null制約に明示的な名前を付けることができないことです。

もちろん、列は複数の制約を持つことができます。制約を次々と書くだけです。

テーブル製品を作成する (
        product_no整数NOT NULL,
        nameテキストNOT NULL,
        価格数値ではないNULLチェック (価格> 0)
    );

注文は関係ありません。制約がチェックされる順序は必ずしも決定されない。

NOT NULL制約は、逆のNULL制約を有する。これは、列がnullでなければならないという意味ではありません。代わりに、これは単に列がnullであるデフォルトの動作を選択します。 NULL制約はSQL標準には存在しないため、ポータブルアプリケーションでは使用しないでください。 (他のいくつかのデータベースシステムと互換性を持つために、PostgreSQLにのみ追加されました。) ただし、一部のユーザーは、スクリプトファイルで制約を簡単に切り替えることができるため、気に入っています。たとえば、次のように開始できます。

テーブル製品を作成する (
        product_no整数NULL,
        nameテキストNULL,
        価格数値NULL
    );

次に、必要な場所にNOTキーワードを挿入します。

説明

ほとんどのデータベース設計では、大部分の列はnullではありません。

ユニークな制約

一意の制約により、列または列のグループに含まれるデータは、テーブル内のすべての行の中で一意になります。構文は次のとおりです。

テーブル製品を作成する (
        product_no整数UNIQUE、
        テキスト名、
        価格数値
    );

列制約として記述された場合、

テーブル製品を作成する (
        product_no整数、
        テキスト名、
        価格数値、
        UNIQUE (product_no)
    );

テーブル制約として記述された場合。

列のグループに一意の制約を定義するには、列名をコンマで区切ったテーブル制約として記述します。

CREATE TABLEの例 (
        整数、
        b整数,
        c整数、
        ユニーク (a, c)
    );

これは、示された列の値の組み合わせがテーブル全体で一意であることを指定しますが、列のいずれかが一意である必要はありません (通常は一意ではありません) 。

通常の方法で、一意の制約に独自の名前を割り当てることができます。

テーブル製品を作成する (
        product_no整数CONSTRAINT must_be_different UNIQUE,
        テキスト名、
        価格数値
    );

一意の制約を追加すると、制約にリストされている列または列のグループに一意のBツリーインデックスが自動的に作成されます。いくつかの行のみをカバーする一意性制限は、一意の制約として記述することはできませんが、一意の部分インデックスを作成することによってそのような制限を強制することは可能です。

一般に、制約に含まれるすべての列の値が等しいテーブル内に2つ以上の行がある場合、一意の制約に違反する。しかし、この比較では2つのヌル値が等しいとはみなされない。これは、一意の制約が存在する場合であっても、制約された列の少なくとも1つにヌル値を含む重複行を格納することが可能であることを意味する。この動作はSQL標準に準拠していますが、他のSQLデータベースがこの規則に従わない可能性があると聞いています。したがって、ポータブル化を目的としたアプリケーションを開発するときは注意してください。

主キー

主キー制約は、列または列のグループをテーブル内の行の一意の識別子として使用できることを示します。これには、値が一意でnullではないことが必要です。したがって、次の2つのテーブル定義は同じデータを受け入れます。

テーブル製品を作成する (
        product_no整数UNIQUE NOT NULL,
        テキスト名、
        価格数値
    );

CREATE TABLEプロダクト (
        product_no整数PRIMARYキー,
        テキスト名、
        価格数値
    );

主キーは複数の列にまたがることができます。構文は一意の制約に似ています。

CREATE TABLEの例 (
        整数、
        b整数,
        c整数、
        主要なキー (a、c)
    );

主キーを追加すると、主キーにリストされている列または列のグループに一意のBツリーインデックスが自動的に作成され、強制的に列にNOT NULLとマークされます。

テーブルは、最大で1つの主キーを持つことができます。 (機能的にほぼ同じものであるが、主キーとして識別できるのは1つだけである、任意の数の一意でヌルでない制約が存在し得る。) リレーショナルデータベース理論では、すべてのテーブルにプライマリキーが必要です。このルールはPostgreSQLでは適用されませんが、通常はそれに従うのが最善です。

主キーは、ドキュメント化の目的とクライアントアプリケーションの両方に役立ちます。例えば、行値の修正を可能にするGUIアプリケーションは、おそらく、行を一意に識別することができるように、テーブルの主キーを知る必要がある。また、主キーが宣言されている場合、データベースシステムが主キーを使用する様々な方法があります。たとえば、主キーは、そのテーブルを参照する外部キーのデフォルトのターゲット列を定義します。

外部キー

外部キー制約では、列 (または列のグループ) の値が、別のテーブルのある行に表示される値と一致する必要があることを指定します。これは、2つの関連テーブル間の参照整合性を維持すると言います。

すでに数回使用した製品テーブルがあるとします。

テーブル製品を作成する (
        product_no整数PRIMARYキー,
        テキスト名、
        価格数値
    );

また、それらの製品の注文を格納するテーブルがあると仮定します。注文テーブルには、実際に存在する商品の注文のみが含まれるようにします。そこで、製品テーブルを参照する注文テーブルで外部キー制約を定義します。

CREATE TABLE orders (
        order_id整数PRIMARY KEY,
        product_no integer REFERENCESプロダクト (product_no) 、
        数量整数
    );

製品テーブルに表示されないNULL以外のproduct_noエントリを持つ注文を作成することは不可能です。

この状況では、注文テーブルは参照テーブルであり、製品テーブルは参照テーブルであると言います。同様に、参照列と参照列があります。

上記のコマンドを次のように短くすることもできます。

CREATE TABLE orders (
        order_id整数PRIMARY KEY,
        product_no整数REFERENCESプロダクト、
        数量整数
    );

これは、列リストがない場合、参照テーブルの主キーが参照列として使用されるためです。

外部キーは、列のグループを制約して参照することもできます。通常通り、テーブル制約形式で記述する必要があります。不自然な構文の例を次に示します。

テーブルの作成t1 (
      整数PRIMARYキー、
      b整数,
      c整数、
      FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
    );

もちろん、制約付き列の数とタイプは、参照される列の数とタイプと一致する必要があります。

テーブルは、複数の外部キー制約を持つことができます。これは、テーブル間の多対多関係を実装するために使用されます。製品と注文に関するテーブルがあるとしますが、1つの注文におそらく多くの製品を含めることを許可したいとします (上記の構造では許可されていません) 。このテーブル構造を使用できます。

テーブル製品を作成する (
        product_no整数PRIMARYキー,
        テキスト名、
        価格数値
    );
テーブル注文を作成する (
        order_id整数PRIMARY KEY,
        shipping_addressテキスト、
        ...
    );

CREATE TABLE order_items (
        product_no整数REFERENCESプロダクト、
        order_id整数REFERENCESオーダー、
        数量整数、
        PRIMARYキー (product_no、order_id)
    );

説明

主キーは、最後のテーブルの外部キーと重複しています。

外部キーは、製品に関連しない注文の作成を許可しないことがわかっています。しかし、それを参照する注文が作成された後に製品が削除された場合はどうなりますか? SQLを使用すると、それも処理できます。直感的に、いくつかのオプションがあります:

参照先製品の削除を禁止する
注文も削除する
他に何か?

これを説明するために、上記の多対多の関係の例で次のポリシーを実装しましょう。誰かがまだ注文によって参照されている製品を削除したい場合 (order_itemsを介して) 、それを許可しません。誰かが注文を削除すると、注文アイテムも削除されます。

テーブル製品を作成する (
        product_no整数PRIMARYキー,
        テキスト名、
        価格数値
    );

テーブル注文を作成する (
        order_id整数PRIMARY KEY,
        shipping_addressテキスト、
        ...
    );

CREATE TABLE order_items (
        product_no整数REFERENCES DELETE RESTRICTの製品、
        DELETE CASCADEのorder_id integer REFERENCES注文、
        数量整数、
        PRIMARYキー (product_no、order_id)
    );

削除の制限とカスケードは、最も一般的な2つのオプションです。RESTRICTは、参照行の削除を防ぎます。NO ACTIONは、制約がチェックされたときに参照行がまだ存在する場合、エラーが発生することを意味します。これは、何も指定しない場合のデフォルトの動作です。 (これらの2つの選択肢の本質的な違いは、NO ACTIONではトランザクションの後半までチェックを延期できますが、RESTRICTでは延期できません。) CASCADEは、参照行が削除されると、参照行を参照する行も自動的に削除されるように指定します。 SET NULLとSET DEFAULTの2つのオプションがあります。これらにより、参照行が削除されると、参照行の参照列がそれぞれヌルまたはそのデフォルト値に設定されます。これらは制約を観察することを許しません。たとえば、アクションがSET DEFAULTを指定しているが、デフォルト値が外部キー制約を満たさない場合、操作は失敗します。

ON DELETEと同様に、参照される列が変更 (更新) されるときに呼び出されるON UPDATEもある。可能なアクションは同じです。この場合、CASCADEは、参照される列の更新された値を参照行にコピーする必要があることを意味します。

通常、参照行は、その参照列のいずれかがヌルである場合、外部キー制約を満たす必要はない。 MATCH FULLが外部キー宣言に追加された場合、参照行は、すべての参照列がnullである場合にのみ制約を満たしていません (したがって、null値と非null値の混合は、MATCH FULL制約に失敗することが保証されます) 。参照行が外部キー制約を満たさないようにしたくない場合は、参照列をNOT NULLとして宣言します。

外部キーは、主キーであるか、一意の制約を形成するか、または非部分的な一意のインデックスからの列である列を参照する必要があります。つまり、参照行が一致するかどうかを効率的に検索できるように、参照列には常にインデックスがあります。参照されるテーブルからの行のDELETEまたは参照される列のUPDATEは、古い値と一致する行の参照テーブルのスキャンを必要とするため、参照する列にもインデックスを付けることをお勧めします。これは必ずしも必要ではなく、インデックスを作成する方法には多くの選択肢があるため、外部キー制約を宣言しても、参照元の列にインデックスが自動的に作成されるわけではありません。

除外制約

除外制約により、指定された演算子を使用して指定された列または式で任意の2つの行を比較すると、これらの演算子の比較の少なくとも1つがfalseまたはnullを返します。構文は次のとおりです。

テーブルサークルを作成する (
        cサークル、
        EXCLUDE USING要点 (c WITH &&)
    );

除外制約を追加すると、制約宣言で指定された型のインデックスが自動的に作成されます。