PostgreSQL 7.4 文書
前のページ	巻戻し	第 5章データ定義	早送り	次のページ

5.4. 制約

データ型は、テーブルに格納するデータの種類を限定するための方法です。しかし、多くのアプリケーションでは、型が提供する制約では精密さに欠けます。たとえば、製品の価格が入る列には、おそらく正数のみを受け入れるようにする必要があります。しかし、正数のみを受け入れるというデータ型はありません。また、他の列や行に関連して列データを制約したい場合もあります。たとえば、製品の情報が入っているテーブルでは、1つの製品番号についての行が2行以上あってはなりません。

このような問題を解決するため、SQLでは列およびテーブルに対する制約を定義することができます。制約によってテーブル内のデータを自由に制御することができます。制約に違反するデータを列に格納しようとすると、エラーとなります。このことは、デフォルト値として定義された値を格納する場合にも適用されます。

5.4.1. 検査制約

検査制約はもっとも一般的な制約の種類です。これを使用して、特定の列の値が任意の式を満たすように指定できます。たとえば、製品価格を必ず正数にするには以下のようにします。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

このように、制約の定義はデフォルト値の定義と同様に、データ型の後にきます。デフォルト値と制約は任意の順序で列挙できます。検査制約の構成は、CHECKキーワードの後に続く括弧で囲まれた式です。検査制約式には、制約される列を含む必要があります。そうしないと、制約はあまり意味のないものになります。

制約に個別に名前を付けることもできます。名前を付けることで、エラーメッセージが分かりやすくなりますし、変更したい制約を参照できるようになります。構文は以下のとおりです。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

上記のように、名前付き制約の指定はCONSTRAINTキーワードで始め、これに識別子、制約定義と続きます。

検査制約では複数の列を参照することもできます。たとえば、通常価格と割引価格を格納する場合に、必ず割引価格が通常価格よりも低くなるようにしたいとします。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

最初の2つの制約は上で説明したとおりです。 3 つ目の制約では新しい構文を使っています。これは特定の列に付加されるのではなく、コンマで区切られた列リスト内の別個の項目として現れます。列定義およびこれらの制約定義は、任意の順序で列挙することができます。

最初の2つの制約を列制約と言います。それに対し3つ目の制約は列定義とは別個に書かれるのでテーブル制約と言います。列制約をテーブル制約として書くことはできますが、その逆はできる場合とできない場合があります。上の例は、以下のように書くこともできます。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

あるいは、次のようにもできます。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

どのようにするかは好みの問題です。

検査制約では、検査式が真またはNULL値と評価された場合に、条件が満たされていることになることを説明しなければなりません。ほとんどの式は、演算項目の1つがNULLであればNULLと評価されるので、制約対象の列ではNULL値を防ぐことはしません。列がNULL値を含まないようにするには、次の非NULL制約を使用します。

5.4.2. 非NULL制約

非NULL制約は単純に、列がNULL値をとらないことを指定します。構文の例は以下のとおりです。

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

非NULL制約は常に列制約として記述されます。非NULL制約はCHECK (column_name IS NOT NULL)という検査制約と機能的には同等ですが、PostgreSQLでは、明示的に非NULL制約を作成する方がより効果的です。このように作成された非NULL制約に明示的な名前を付けられないのが欠点です。

もちろん、1つの列に複数の制約を適用することもできます。そのためには、次々と制約を書いていくだけです。

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

順序は関係ありません。書かれた順序とチェックされる順序は必ずしも同じではありません。

NOT NULL制約に対し、逆のパターンであるNULL制約があります。これは、列がNULLでなければならないということではありません。そのような制約は意味がありません。この制約は、列がNULLであってもよいというデフォルトの振舞いを定義するだけのものです。 NULL制約は標準SQLでは定義されていませんので、移植予定のアプリケーションでは使用すべきではありません。 (これは、PostgreSQLと他のデータベースシステムとの互換性のために追加された機能にすぎません。) もっとも、スクリプトファイルでの制約の切り替えが簡単であるという理由でこの機能を歓迎するユーザもいます。たとえば、最初に

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

と書いてから、必要な場所にNOTキーワードを挿入することができます。

ティップ: ほとんどのデータベース設計において、列の大多数をNOT NULLとマークする必要があります。

5.4.3. 一意性制約

一意性制約によって、列あるいは列のグループに含まれるデータが、テーブル内のすべての行で一意であることを確実にします。列制約の場合の構文は以下のとおりです。

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

また、テーブル制約の場合の構文は

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

となります。

一意性制約が列のグループを参照する場合、各列はコンマで区切って列挙します。

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

一意性制約には名前を割り当てることもできます。

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

一般に、制約の対象となる列について、同じ値をもつ行が(少なくとも)2行以上ある場合は、一意性制約違反になります。しかし、ここではNULL値は等価とは見なされません。つまり、一意性制約があったとしても、制約対象の列の少なくとも1つにNULL値をもつ行を無限に格納することができるということです。この振る舞いは標準SQLに準拠していますが、この規則に従わないSQLデータベースもあるそうです。ですから、移植する予定のアプリケーションを開発する際には注意してください。

5.4.4. プライマリキー

技術的には、プライマリキー制約は単純に一意性制約と非NULL制約を組み合わせたものです。つまり、次の2つのテーブル定義は同じデータを受け入れます。

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

プライマリキーでも複数の列を制約することができ、その構文は一意性制約に似ています。

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

プライマリキーは、列または列のグループを、テーブル内で行の一意の識別子として使用できることを示します。 (このことは、プライマリキーの定義からの直接の帰結です。一意性制約ではNULL値を除外しないため、一意の識別子が提供されないことに注意してください。) これは文書化、および、クライアントアプリケーションの両方の面で役に立ちます。たとえば、行値の変更が可能なGUIアプリケーションが行を一意的に特定するためには、おそらくテーブルのプライマリキーを知る必要があります。

1つのテーブルに含めることのできるプライマリキーの最大数は1つです。 (一意性制約および非NULL制約には個数の制限はありません。) リレーショナルデータベース理論では、すべてのテーブルにプライマリキーが 1 つ必要とされています。この規則はPostgreSQLでは強制されませんが、大抵の場合はこれに従うことが推奨されます。

5.4.5. 外部キー

外部キー制約は、列(または列のグループ)の値が、他のテーブルの行の値と一致しなければならないことを指定します。これによって関連する2つのテーブルの参照整合性が維持されます。

これでまで何度か例に使用したproductsテーブルについて考えてみます。

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

また、これらの製品に対する注文を格納するテーブルも作成済みだとしましょう。この注文のordersテーブルには実際に存在する製品の注文のみを格納したいと思っています。そこで、productsテーブルを参照するordersテーブルに外部キー制約を定義します。

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

これで、productsテーブルに存在しないproduct_no項目を使用して注文を作成することはできなくなります。

このような場合に、ordersテーブルのことを参照テーブル、productテーブルのことを被参照テーブルと呼びます。同様に、参照列と被参照列もあります。

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

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

列リストがないため、被参照テーブルのプライマリキーが被参照列として使用されます。

外部キーでも、列のグループを制約したり参照したりすることもできます。これもまた、テーブル制約の形式で記述する必要があります。以下は、説明のための非現実的な例です。

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

もちろん、制約される列数および型は、被参照列の数および型と一致しなければなりません。

テーブルには複数の外部キー制約を含めることができます。このことはテーブル間の多対多関係を実装するために使用されます。たとえば、製品と注文に関するそれぞれのテーブルがある場合に、複数の製品にまたがる注文を可能にしたいとします。 (上の例の構造では不可能です。) この場合、次のテーブル構造を使用できます。

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

最後のテーブルで、プライマリキーと外部キーが重なっていることにも注目してください。

外部キーが製品に関連付けられていない注文の作成を許可しないことは、すでに説明したとおりです。しかし、ある注文で参照していた製品が、注文後に削除されたらどうなるでしょう。 SQLではこのような場合を指定することもできます。直感的に、いくつかのオプションが考えられます。

参照される製品の削除を許可しない
注文も一緒に削除する
他にもありますか？

具体例として、上の例の多対多関係に次のポリシーを実装してみましょう。 (order_itemsによって)注文で参照されたままの製品を除去しようしても、この操作を行えないようにします。注文が除去されると、注文項目も除去されます。

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

削除の制限およびカスケードという2つは、もっとも一般的なオプションです。 RESTRICTはNO ACTIONと記述することもでき、何も指定しない場合は、これがデフォルトとなります。他にも、プライマリキーが削除されたときの外部キー列の処理を指定するためのオプションが2つあります。 SET NULLとSET DEFAULTです。これらのオプションがあるからといって、制約に注意する必要がなくなるわけではありません。たとえば、アクションで SET DEFAULT を指定しても、そのデフォルト値が外部キーの制約を満たさない場合、プライマリキーの削除は失敗します。

ON DELETEに似たもので、プライマリキーが変更(更新)されたときに呼び出されるON UPDATEもあります。これらが行えるアクションは同じです。

データの更新および削除について詳しくは、第6章を参照してください。

最後に、外部キーはプライマリキーまたは一意性制約のいずれかである列を参照している必要があります。外部キーが一意性制約を参照している場合、NULL値をどのようにマッチさせるかに関する他の方法がいくつかあります。これらについては、リファレンス文書のCREATE TABLEに説明があります。

前のページ	ホーム	次のページ
デフォルト値	上に戻る	継承