[DBデザイン#40] データベースに与える制約

データベースを稼働させるには、色々な前提があるということはこれまでにも説明してきました。例えば、主キーという存在が必要であるということは、主キーとして認識しているフィールドのセットに対して、それらが重複するレコードが存在しないことが前提となります。これを、「後はよろしく」と言われても、実装が込み入るとか、バグがあったらどうするなど懸念点ばかりが思いつきます。しかしながら、データベースには、前提が必ず満たされているということを保証するための制約の機能があります。この機能を利用することで、前提が崩れるデータはデータベースには存在しないと保証できると思っていただいていいでしょう。データベースにバグはないのと思われるかもしれませんが、今時のデータベースソフトは長年の開発によって、こうした「前提の前提」のような発生しては大いに困ってしまうような箇所のバグはまずないと思ってOKです。

前提を満たさない状況にしようとしたときにエラーを出してデータの書き込みをしないというのが一般的な動作になりますが、システム側はそうしたデータベースのエラーを適切に処理する必要があります。システムが止まってはいけません。そしておそらく書き込もうとしたデータが正しくないわけで、そのことがわかるようなユーザインタフェースを作る必要があることも言うまでもありません。結果的に、そうしたユーザインタフェースをまともに作るとなると、「あらかじめ前提を満たすかどうかをチェックする」と言うことに究極的にはなってしまって、それじゃあ別にデータベースに制約機能なくてもいいじゃないかと思ってしまうかもしれません。このことはむしろ、データベースとUI側の両方に制約をかけることで、より確実にシステムが動作すると言うことで前向きに捉えるべきでしょう。いずれにしても、データベースが制約をかけているから「大丈夫」と放置するのではなく、きちんとシステム側の特にUIにもきちんとした対処は必要であることは認識すべき事柄です。

データベースのフィールドは、NULLと言う特殊な値を取ることができますが、まず、「NULLにはならない」と言う制約が付与できます。CREATE TABLE文のフィールド定義で、NOT NULLと言うキーワードを付与するのが一般的です。NULLの書き込みは意図的なことをしない限りは通常はなく、NOT NULLはフィールドの初期値に関して考えておくことになるのではないでしょうか。一般にはフィールドの初期値がNULLになりますが、この制約をつけても初期値はNULLのままです。つまり、既定値は許されない状態になるので、必ず値を与えるか、フィールドの定義部分で「個数 INT NOT NULL DEFAULT 1,」などとして、DEFAULTに続いて値を指定するか、何らかの対処が必要になります。

そして、主キーフィールド等で利用できる「一意制約」の機能もあります。この機能はインデックスの仕組みを利用するのが一般的です。インデックスは、フィールドのデータがどのレコードにあるのかを特定できる情報をデータベース側で自動的に作ってくれる機能です。その実現方法も興味深いところですが、完全にブラックボックス的に動いてくれるので、要するに「検索結果を早く提供してくれる機能」と割り切って考えれば良いでしょう。例えば、文字列を検索する場合、インデックスがないとしたら、全データを最初から最後までチェックしないといけません。インデックスがあると、その文字列があるレコードは、これとこれと…と特定してくれるので、当然ながら検索は速くなります。現在のコンピュータ環境では、ちょっとしたデータであればあまりインデックスの効果はないと思っている方もいらっしゃると思いますが、長いデータや大量のデータでは十分にインデックスによるスピードアップは期待できます。少量のデータだからといってインデックスがあれば遅くなるようなことはないので、検索に使うフィールドにはインデックスを作っておくのは基本中の基本になります。仮にインデックスを作らなくてもサクサク動いているとしても、インデックスのない検索はデータに比例して時間がかかるようになるため、いつか耐えられないくらいになってしまう可能性を秘めています。問題がないと思っても将来に備えてインデックスを作っておくべきです。

このインデックスは、値の重複を許す場合と、許さない場合があり、インデックス作成時に指定をします。もちろん、前者は検索の高速化が主な用途ですが、後者は主キーに設定するために利用できます。そして、フィールドは、1つだけではなく、フィールドの組み合わせに対してもインデックスは作成できます。例えば、{商品名, 顧客名, 個数}に対して重複を許さないインデックスを作成すると、この3つのフィールドが全部同じレコードは作成できなくなります。商品名が同じものがいくつもあるのは構いません。ちなみに、レコードを追加したりフィールドを変更した時に、インデックスは更新されるのですが、インデックスが正しく更新されないと、追加や変更の処理もキャンセルされてしまいます。インデックスはテーブルのデータと連携をしていて、「制約」として動作すると言うわけです。もちろん、データベースからはエラーがレポートされるので、適切に処理をしなければなりません。

MySQLを例にとって説明しますが、例えば、次のようなテーブル定義があったとします。とりあえず、販売明細IDを主キーとし、連番を入力することにします。記述方法の1つとして、( ) 内の最後にPRIMARY KEYとしてどのフィールドが主キーかを指定しています。これにより、自動的に重複を許さないインデックスが作成され、フィールド「販売明細ID」の値と連携することになります。もちろん、ある数値を与えれば、どのレコードなのかと言うことを瞬時に返すインデックスの意味も持ちますが、販売明細IDに既に入力してある値を新たに挿入しようとしてもエラーを返す返す一意制約のためのインデックとしても機能します。ちなみに、これによりPRIMARYと言う名前のインデックスが作成されます。インデックスは、テーブル名とフィールド名の情報を持ち、テーブルごとに管理されるので、結果的にデータベース内にPRIMARYと言う名前のインデックスがいくつも作られることになるでしょう。

# DROP TABLE IF EXISTS 販売明細;
CREATE TABLE 販売明細 (
  販売明細ID INT AUTO_INCREMENT,
  納品書ID INT NOT NULL,
  商品ID INT,
  個数 INT NOT NULL DEFAULT 1,
  PRIMARY KEY(販売明細ID)
);

MySQLでは主キーを設定すると自動的にインデックスが作られるので、例えば、「CREATE UNIQUE INDEX 販売明細_販売明細ID ON 販売明細 (販売明細ID);」のようなSQL文で新たにインデックスを作成する必要はありません。ちなみに、重複を許さないという特性は、UNIQUEと言うキーワードで指定をします。INDEXの後はインデックス名、そして、ONの後に「テーブル名(フィールド名)」と言う記述を行います。

なお、ここで、モデルの上で、同一の納品書の明細には、1つの商品は1行だけ登場するか、全く登場しないかどのどちらかであるという制約があったとします。そうすると、次のような重複を許さないインデックスを作成しておくことで、主キーの制約をかけることができます。おそらく、納品書のUIで、明細行を追加し(その時には既に納品書IDは確定しているので入力済みになる)、商品を選択したときに既に同じ商品がその納品書の明細に存在していれば、制約違反のエラーが出てきます。それを避けるために、商品の選択肢に既に入力してある商品が出てこないようにするのが1つの方法ですが、そこまでの対処をUIで行うと、UI側とデータベース側の2重対処をしたことになります。そのことが冗長なのか、それとも確実な動作を支える実装なのかは意見が別れるところですが、設計とはそういう場合にどうするのかと言うこことを決める作業に他なりません。そのようなことを早い段階から検討できるようになりましょう。

CREATE UNIQUE INDEX 販売明細_主キー ON 販売明細 (納品書ID, 商品ID);

なお、UNIQUEなインデックスを設定するだけだと、NULLは許可されている状態になります。前述のようなユーザインタフェースだと、商品IDがNULLの期間が発生するので、NULL許可はワークフロー上は必要であるとも言えます。ですが、いずれにしても、NULLにならないようにしたいということであれば、テーブル定義にNOT NULLを指定します。MySQLのUNIQUE INDEXは、例えばこの場合だと、納品書=1、商品ID=NULLというレコードは複数存在できるようになっています。つまり、NULLの値がある場合には、一意性の評価はしないと見ることができます。これは、NULL = NULLはFALSEであると評価できるところから来ており、NULLは「どのレコードも異なる」と判定されてしまいます。つまり、主キーフィールドの1つでもNULLがあれば、見かけは同一のレコードも作成できてしまいます。もちろん、主キーフィールドはNULLにならないと言うのが「原則」という考え方もできるのですが、ワークフロー上NULLである期間を許すとなると、落とし所は難しくなります。もちろん、現実の案件では、色々な要求に対する実現手法や実現度合いを見計らいトレードオフを行なって最終決定するしかないでしょう。

さらに「外部キー制約」のこともあるのですが、ちょっと長くなったので、これは次回に説明しましょう。