gihyo.jpサイトのロゴ

初めてのデータベース設計

第5回制約とインデックス

2007-11-02

前回は、テーブルの列のデータ型について説明しました。列のデータ型を決定すればテーブルを定義することができます。

なお、テーブルを定義する際には、列のデータ型を決するだけではなく、制約やインデックスといったものも必要によって定義します。今回は、テーブルに格納するデータを限定する制約と、データベースの性能を向上させるインデックスについて説明します。

制約とは

制約とは、テーブルに格納するデータを限定する方法です。データ型によって整数であるか、文字列であるかといったデータの種類を限定することができます。しかし、それでは正数のデータのみを受け付けるとか、同じデータが重複してはいけないとかといった制限を行うことができません。

このような制約を列やテーブルに対して定義することができます。制約に違反するデータを格納しようとするとエラーとなります。

アプリケーションにおいていくらデータをチェックするとしても、最終的にデータの格納されるデータベースにおいて不整合が発生しないように、テーブルを定義する際には考えられる制約はすべて定義したほうがいいでしょう。

ここでは、第3回に登場したテーブルを例にとって制約について説明します。

検査制約

検査制約では、列のデータが論理値（真、または偽）を満足させる式として制約を定義します。

たとえば、「⁠製品（products⁠）⁠」テーブルはPostgreSQLでは次のように定義することができますが、

CREATE TABLE products (
    id integer,
    name text,
    price integer
);

製品の価格は正数のみであると考えると、このテーブルの「価格（price⁠）⁠」列に格納するデータは0より大きな整数であるという制約として以下のように定義することができます。

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

非NULL制約

非NULL制約は、列のデータがNULL値ではないことを保証する制約です。NULL値とは未定義のデータのことであり、非NULL制約が定義されていない列には、そのデータ型に基づくデータ以外にNULL値を格納することができます。

たとえば、製品には製品番号や製品名、価格が必ず指定されていると考えると、「⁠製品（products⁠）⁠」テーブルのすべての列に対して非NULL制約を以下のように定義することができます。

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

一意性制約

一意性制約は、列のデータが重複しないことを保証する制約です。なお、NULL値については重複してデータを格納したとしても、一意性制約に違反することにはなりません。

たとえば、製品番号によって製品を一意に特定できると考えると、「⁠製品番号（id⁠）⁠」列には非NULL制約に加えて一意性制約を以下のように定義することができます。

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

NULL値の重複が一意性制約に違反しないことは標準SQL規格に準拠していますが、SQL SeverなどのようにNULL値の重複も許容しないRDBMSがあるので注意する必要があります。

主キー制約

主キー制約は、一意性制約と非NULL制約を組み合わせたものであり、列のデータが重複せず、NULL値ではないことを保証する制約です。テーブルにおいて主キーとなる列に対して定義します。

たとえば、「⁠製品（products⁠）⁠」テーブルでは「製品番号（id⁠）⁠」列が主キーとなるため、これまで一意性制約と非NULL制約を定義していましたが、以下のように主キー制約として定義することができます。

  CREATE TABLE products (
      id integer PRIMARY KEY,
      name text NOT NULL,
      price integer NOT NULL CHECK (price > 0)
  );

外部キー制約

外部キー制約は、列のデータが他のテーブルのデータを参照する際にそれらのデータが一致していることを保証する制約です。テーブルにおいて外部キーとなる列に対して定義します。

外部キー制約を定義することにより、参照先のテーブルに存在しないデータを格納したり、参照されているデータを参照先のテーブルから削除したりする際、どのように振る舞うかといったことを指定することができます。標準ではそのような場合にはエラーとなります。

たとえば、「⁠注文明細（order_details⁠）⁠」テーブルでは、「⁠注文（orders⁠）⁠」テーブルの「注文番号（id⁠）⁠」列と「製品（products⁠）⁠」テーブルの「製品番号（id⁠）⁠」列をそれぞれ外部キーとして参照しています。これらの列は以下のように外部キー制約として定義することができます。

  CREATE TABLE order_details (
      order_id integer REFERENCES order (id),
      product_id integer REFERENCES product (id),
      quantity integer CHECK (quantity > 0),
      PRIMARY KEY (order_id, product_id)
  );

インデックスとは

インデックスとは、データベースの性能を向上させる方法の1つです。インデックスを日本語に直訳すると索引となります。インデックスによって検索の性能が向上することは、書籍から目的のページを探す際に最初から1ページずつ探していくより索引から探したほうがすばやく見付けることができる、ということを思い出せばわかりやすいと思います。

多くのRDBMSでは、主キー制約や一意性制約が定義された列には自動的にインデックスが定義されます。それ以外の列にも、検索や結合の条件としてよく使用されるものにインデックスを定義することによって、検索の性能を向上させることができます。

ただし、インデックスは単純に定義すればいいというものではありません。それは、インデックスによって検索の性能を向上させることができる反面、データを更新する際にはインデックスも更新する必要があり、更新の性能が低下するためです。

どの列に対してインデックスを定義するべきかということは、どのような問い合わせがどのくらいの頻度で実行されるかということによって変化するため、アプリケーションの設計が完了してからインデックスを追加したとしても遅くはありません。

本特集では、データベース設計の基本についてキーワードをかい摘んで説明してきました。多少説明が駆け足になってしまいましたが、全体的な流れについては理解してもらえたと思います。

しかし、データベース設計はそれだけで一冊の書籍になってしまうほどいろいろな手法があり、非常に奥の深いものです。本特集をきっかけにそういった書籍を手に取ってもらい、データベース設計に関する知識や技術を深めてもらえるといいと思います。

おすすめ記事

記事・ニュース一覧