PostgreSQLの列とテーブル制約入門

列制約

列制約は、単一の列に付加される制約です。これらは、列に提案された値が有効かどうかを判断するために使用されます。列制約は、入力が基本的な型要件（値がint列の整数であることを確認するなど）に対して検証された後に評価されます。

列制約は、単一のフィールドに限定された要件を表現するのに最適です。制約条件を、関係する列に直接付加します。たとえば、personテーブルのage制限をモデル化するには、列名とデータ型の後に制約を追加します。

CREATE TABLE person (
    . . .
    age int CHECK (age >= 0),
    . . .
);

このスニペットは、personテーブルを定義しており、列の1つがageというint型です。ageは0以上である必要があります。列制約は、影響を与える列に追加の要件として追加されるため、理解しやすいです。

テーブル制約

もう一方の種類の制約は、テーブル制約と呼ばれます。テーブル制約は、列制約ができる制限をすべて表現できますが、さらに複数の列が関与する制限を表現できます。テーブル制約は、特定の列に付加されるのではなく、テーブルの独立したコンポーネントとして定義され、テーブルの任意の列を参照できます。

先ほど見た列制約は、次のようにテーブル制約として表現できます。

CREATE TABLE person (
    . . .
    age int,
    . . .
    CHECK (age >= 0)
);

基本的な構文は同じですが、制約は別々にリストされています。テーブル制約が複合的な制限を導入できるという利点を活かすために、論理AND演算子を使用して、異なる列からの複数の条件を結合できます。

たとえば、銀行データベースでは、qualified_borrowersというテーブルで、ローンを申し込む資格を得るために、個人が既存の口座を持っているかどうか、および担保を提供できる能力があるかどうかを確認する必要がある場合があります。これら両方を同じチェックに含めるのが理にかなっているかもしれません。

CREATE TABLE qualified_borrowers (
    . . .
    account_number int,
    acceptable_collateral boolean,
    . . .
    CHECK (account_number IS NOT NULL AND acceptable_collateral = 't')
);

ここでは、CHECK制約を再度使用して、account_numberがnullではなく、融資担当者がクライアントが許容可能な担保を持っているとマークしているかどうかをacceptable_collateral列をチェックすることで確認します。複数の列がチェックされているため、テーブル制約が必要です。

ここでは、新しいテーブルを作成するためにこれらの例で主にCREATE TABLE SQLコマンドを使用しますが、ALTER TABLEを使用して既存のテーブルに制約を追加することもできることに言及しておきます。ALTER TABLEを使用する場合、デフォルトでは、新しい制約により、テーブルに現在存在する値が新しい制約に対してチェックされます。NOT VALID句を含めることで、この動作をスキップできます。

デフォルトの制約名

上記の構文を使用して制約を作成すると、PostgreSQLは妥当ですが、曖昧な名前を自動的に選択します。上記のqualified_borrowersテーブルの場合、PostgreSQLは制約にqualified_borrowers_checkという名前を付けます。

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR:  new row for relation "qualified_borrowers" violates check constraint "qualified_borrowers_check"
DETAIL:  Failing row contains (123, f).

この名前は、制約が違反した場合に、テーブルと制約のタイプに関する情報を提供します。ただし、テーブルに複数の制約が存在する場合、トラブルシューティングに役立つ、より記述的な名前が役立ちます。

カスタム制約名

制約定義の前にCONSTRAINTキーワードとそれに続く名前を付けることで、制約の名前をオプションで指定できます。

カスタム名を追加するための基本的な構文は次のとおりです。

CONSTRAINT <constraint_name> <constraint_type_and_details>

たとえば、qualified_borrowersテーブルの制約にloan_worthinessという名前を付けたい場合は、代わりに次のようにテーブルを定義できます。

CREATE TABLE qualified_borrowers (
    . . .
    account_number int,
    acceptable_collateral boolean,
    . . .
    CONSTRAINT loan_worthiness CHECK (account_number IS NOT NULL AND acceptable_collateral = 't')
);

これで、制約に違反すると、より記述的なラベルが表示されます。

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR:  new row for relation "qualified_borrowers" violates check constraint "loan_worthiness"
DETAIL:  Failing row contains (123, f).

列制約にも同じ方法で名前を付けることができます。

CREATE TABLE teenagers (
    . . .
    age int CONSTRAINT is_teenager CHECK (age >= 13 AND age <= 19),
    . . .
);

チェック制約

チェック制約は、列またはテーブルの値を含む式をブール値に評価するように指定できる汎用制約です。

チェック制約の例をいくつかすでに見てきました。チェック制約は、キーワードCHECKで始まり、その後に括弧で囲まれた式を提供します。列制約の場合、これはデータ型宣言の後に配置されます。テーブル制約の場合、これらは相互作用する列が定義された後であればどこにでも配置できます。

たとえば、2019年の長編賞にノミネートされ、資格のある映画を含むfilm_nominationsテーブルを作成できます。

CREATE TABLE film_nominations (
    title text,
    director varchar(250),
    release_date date CHECK ('01-01-2019' <= release_date AND release_date <= '12-31-2019'),
    length int,
    votes int,
    CHECK (votes >= 10 AND length >= 40)
);

1つの列チェック制約があり、release_dateが2019年以内であることをチェックします。その後、テーブルチェック制約があり、映画がノミネートされるのに十分な票を獲得し、「長編」カテゴリの資格があることを保証します。

チェック制約を評価する場合、許容可能な値はtrueを返します。新しいレコードの値がすべての型要件と制約を満たしている場合、レコードはテーブルに追加されます。

INSERT INTO film_nominations VALUES (
    'A great film',
    'Talented director',
    '07-16-2019',
    117,
    45
);

INSERT 0 1

falseを生成する値は、制約が満たされなかったことを示すエラーを生成します。

INSERT INTO film_nominations VALUES (
    'A poor film',
    'Misguided director',
    '10-24-2019',
    128,
    1
);

ERROR:  new row for relation "film_nominations" violates check constraint "film_nominations_check"
DETAIL:  Failing row contains (A poor film, Misguided director, 2019-07-16, 128, 1).

この場合、映画は必要な票数を除くすべての条件を満たしています。PostgreSQLは、最後のテーブルチェック制約に合格しないため、送信を拒否します。

非NULL制約

NOT NULL制約は、はるかに焦点を絞っています。列内の値がnullではないことを保証します。これは単純な制約ですが、非常に頻繁に使用されます。

CREATE TABLE national_capitals (
    country text NOT NULL,
    capital text NOT NULL,
);

INSERT INTO national_capitals VALUES (
    NULL,
    'London',
);

ERROR:  null value in column "country" violates not-null constraint
DETAIL:  Failing row contains (null, London).

CREATE TABLE national_capitals (
    country text,
    capital text,
    CHECK (country IS NOT NULL AND capital IS NOT NULL)
);

ユニーク制約

UNIQUE制約は、PostgreSQLに、列内の各値を繰り返してはならないことを指示します。これは、複数のレコードで同じ値を持つことが不可能である必要があるさまざまなシナリオで役立ちます。

たとえば、あらゆる種類のIDを扱う列は、定義上、一意の値を持つ必要があります。社会保障番号、学生または顧客ID、または製品UPC（バーコード番号）は、特定の人やアイテムを区別できない場合、役に立たなくなります。

UNIQUE制約は、列レベルで指定できます。

CREATE TABLE supplies (
    supply_id integer UNIQUE,
    name text,
    inventory integer
);

これらは、テーブル制約としても指定できます。

CREATE TABLE supplies (
    supply_id integer,
    name text,
    inventory integer,
    UNIQUE (supply_id)
);

UNIQUEテーブル制約を使用する利点の1つは、列の組み合わせに対して一意性チェックを実行できることです。これは、PostgreSQLが一緒に評価する必要がある2つ以上の列を指定することで機能します。個々の列の値は繰り返される可能性がありますが、指定された値の組み合わせは一意である必要があります。

例として、以前に使用したnational_capitalsテーブルを振り返ってみましょう。

CREATE TABLE national_capitals (
    country text NOT NULL,
    capital text NOT NULL,
);

同じペアに対して複数のレコードを追加しないようにしたい場合は、ここにUNIQUE制約を列に追加できます。

CREATE TABLE national_capitals (
    country text NOT NULL UNIQUE,
    capital text NOT NULL UNIQUE,
);

これにより、国と首都の両方が各テーブルに1回だけ存在することが保証されます。ただし、複数の首都を持つ国もあります。これは、同じcountry値を持つ複数のエントリがある可能性があることを意味します。これらは現在の設計では機能しません。

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "national_capitals_country_key"
DETAIL:  Key (country)=(Bolivia) already exists.

個々の列で繰り返しの値が許可されながら、重複エントリが発生しないようにしたい場合でも、countryとcapitalの組み合わせに対する一意性チェックで十分です。

CREATE TABLE national_capitals (
    country text,
    capital text,
    UNIQUE (country, capital)
);

これで、ボリビアの首都の両方をエラーなしでテーブルに追加できます。

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

INSERT 0 1
INSERT 0 1

ただし、同じ組み合わせを2回追加しようとすると、制約によって引き続きキャッチされます。

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "national_capitals_country_capital_key"
DETAIL:  Key (country, capital)=(Bolivia, Sucre) already exists.

主キー制約

PRIMARY KEY制約は、特別な目的を果たします。これは、列をテーブル内のレコードを一意に識別するために使用できることを示します。これは、信頼できる一意性が必要であり、すべてのレコードがその列に値を持っている必要があることを意味します。

主キーは、必須ではないすべてのテーブルに推奨され、すべてのテーブルは1つの主キーのみを持つことができます。主キーは主に、テーブル内の個々のレコードを識別、取得、変更、または削除するために使用されます。これにより、ユーザーと管理者は、PostgreSQLによって正確に1つのレコードと一致することが保証されている識別子を使用して操作をターゲットにできます。

例として、以前に見たsuppliesテーブルを使用してみましょう。

CREATE TABLE supplies (
    supply_id integer UNIQUE,
    name text,
    inventory integer
);

ここでは、supply_idが一意である必要があることを特定しました。この列を主キーとして使用したい場合（一意性と非null値を保証）、UNIQUE制約をPRIMARY KEYに簡単に変更できます。

CREATE TABLE supplies (
    supply_id integer PRIMARY KEY,
    name text,
    inventory integer
);

このようにして、特定のサプライ品の在庫量を更新する必要がある場合は、主キーを使用してターゲットにすることができます。

INSERT INTO supplies VALUES (
    38,
    'nails',
    5
);
UPDATE supplies set inventory = 10 WHERE supply_id = 38;

INSERT 0 1
UPDATE 1

多くのテーブルでは単一の列を主キーとして使用していますが、テーブル制約として、列のセットを使用して主キーを作成することもできます。

national_capitalsテーブルは、これを実証するのに適した候補です。既存の列を使用して主キーを作成したい場合は、UNIQUEテーブル制約をPRIMARY KEYに置き換えることができます。

CREATE TABLE national_capitals (
    country text,
    captial text,
    PRIMARY KEY (country, capital)
);

外部キー制約

外部キーは、あるテーブル内の列であり、別のテーブル内の列値を参照します。これは、テーブルに関連データが含まれているさまざまなシナリオで望ましく、多くの場合必要です。データベースが個別のテーブルに格納されたデータを簡単に接続および参照できるこの機能は、リレーショナルデータベースの主要な機能の1つです。

たとえば、個々の注文を追跡するためのordersテーブルと、顧客の連絡先情報と情報を追跡するためのcustomersテーブルがある場合があります。顧客は多くの注文を持つ可能性があるため、この情報を別々に配置するのは理にかなっています。ただし、より複雑な操作を可能にするために、これら2つのテーブルのレコードを簡単にリンクできるようにすることも理にかなっています。

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    first_name text,
    last_name text,
    phone_number bigint,
);

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    order_date date,
    customer integer REFERENCES customers
);

INSERT INTO orders VALUES (
    100,
    '11-19-2019',
    300
);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_fkey"
DETAIL:  Key (customer)=(300) is not present in table "customers".

INSERT INTO customers VALUES (
    300,
    'Jill',
    'Smith',
    '5551235677'
);
INSERT INTO orders VALUES (
    100,
    '11-19-2019',
    300
);

INSERT 0 1
INSERT 0 1

CREATE TABLE example (
    . . .
    column type REFERENCES other_table (column)
);

CREATE TABLE example (
    . . .
    FOREIGN KEY (column1, column2) REFERENCES other_table (column1, column2)
);

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    order_date date,
    customer integer REFERENCES customers ON DELETE CASCADE
);

除外制約

最後に説明する制約のタイプは、除外制約です。CHECKのような制約は、各行の有効性を個別にチェックできますが、除外制約は、複数の行の値を相互にチェックします。UNIQUE制約は、問題の列または列に対して各行が異なる値を持つことをチェックする特定のタイプの除外制約です。

たとえば、除外制約を使用して、次のような除外を使用して2つの日付範囲間に重複がないことを確認できます。

CREATE EXTENSION btree_gist;

CREATE TABLE bookings (
    room int,
    booking_start date,
    booking_end date,
    EXCLUDE USING gist (
        room WITH =,
        daterange(booking_start, booking_end, '[]') WITH &&
    )
);

ここでは、部屋番号と予約の開始日と終了日を含むホテル予約のテーブル作成ステートメントがあります。まず、データベースで使用するインデックスメソッドが有効になっていることを確認するためにCREATE EXTENSION btree_gistを指定します。その後、EXCLUDE USING構文を使用して除外制約を追加します。比較するために値をインデックス化してアクセスする方法をPostgreSQLに指示するインデックスメソッドとしてgistを指定します。

次に、アイテムを比較する方法をリストアップします。 room の値は、イコール記号で比較するように指定します。これは、制約が同じ room を持つ2つの行に対してのみ一致することを意味します。daterange は、booking_start 列と booking_end 列をまとめて日付範囲としてチェックします。オプションの3番目のパラメータとして [] を含めることで、範囲を包括的に比較することを示します。&& 演算子は、日付範囲が重複をチェックする必要があることを指定します。

したがって、全体として、この制約は同じ部屋が重複する日付で予約されないようにします。