PostgreSQLのカラムとテーブルの制約の紹介

カラム制約

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

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

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

このスニペットは、カラムの1つがageという名前のintであるpersonテーブルを定義しています。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 制約

CHECK制約は、カラムまたはテーブルの値を含む式を指定し、それがブール値に評価されることを可能にする汎用的な制約です。

チェック制約の例をいくつか既にご覧になりました。チェック制約はキーワード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)
);

まず、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は最終的なテーブルチェック制約をパスしないため、提出を拒否します。

NOT 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 制約

UNIQUE制約は、カラム内の各値が繰り返されてはならないことをPostgreSQLに伝えます。これは、複数のレコードに同じ値が存在してはならない多くの異なるシナリオで役立ちます。

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

A 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,
);

これにより、国と首都の両方が各テーブルに一度だけ存在することが保証されます。しかし、複数の首都を持つ国もあります。これは、同じ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 制約

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

FOREIGN KEY 制約

外部キーは、あるテーブル内のカラムが別のテーブル内のカラム値を参照するものです。これは、テーブルが関連データを含むさまざまなシナリオで望ましく、しばしば必要とされます。データベースが別々のテーブルに格納されたデータを簡単に接続し、参照できるこの機能は、リレーショナルデータベースの主要な特徴の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
);

EXCLUSION 制約

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

たとえば、次のような排他制約を使用して、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構文を使用して排他制約を追加します。インデックスメソッドとしてgistを指定し、PostgreSQLに値を比較するためのインデックス作成とアクセス方法を伝えます。

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

つまり、この制約は、同じ部屋が重複する日付で予約されないようにします。