[DBデザイン#41] データベースに与える制約-続き

前回はNULL制約、そして一意制約について説明しました。もう1つ重要な制約は、外部キー制約です。サンプルに出てきた販売明細と商品の間では、外部キーフィールドである販売明細の商品IDフィールドには、主キーである商品の商品IDの値を入れることで、販売明細の１つのレコードにおいてどの商品を販売したのかを記録するということができました。この時、外部キーに、主キーにある値以外のものを入力できなくするのが外部キー制約です。例えば、次のようにテーブル定義の時に、FOREIGN KEYで外部キー制約を定義します。CONSTRAINTキーワードを使った書き方もありますが、省略することが多いと思われます。FREIGN KEYでの記述では、まず、商品IDフィールドが外部キー制約をかけると言うことを指定し、REFERENCES以降で、制約をかけたフィールドに入るべき値が用意されているテーブルのフィールドを記述します。ここでは１つのフィールドだけですが、複数のフィールドを指定することもできます。

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

# DROP TABLE IF EXISTS 商品;
CREATE TABLE 商品 (
  商品ID INT AUTO_INCREMENT,
  商品名 VARCHAR(50),
  単価 INT,
  PRIMARY KEY(商品ID),
);

INSERT INTO 商品(商品名, 単価) VALUES('A', 500);
INSERT INTO 商品(商品名, 単価) VALUES('B', 600);

最後のINSERTを実行すると、商品IDが、1と2のレコードが作成されます。その状態で、販売明細に新しいレコードを作る時に、商品IDに4などの商品テーブルに存在しない数値を指定すると、エラーとなって挿入処理はキャンセルされます。こうして、「参照先が存在しない」と言う状況を作らないようにガードしてくれる機能がデータベースにあります。

mysql> insert into 販売明細 (納品書ID, 商品ID) values(1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`販売明細`, CONSTRAINT `販売明細_ibfk_1` FOREIGN KEY (`商品ID`) REFERENCES `商品` (`商品ID`))

ただし、ここでNULLの扱いをきちんと把握しなければなりません。外部キー制約に関してはNULLは対象外となっていて、NULLの入力は可能です。NULLはもちろん、参照先で何かのレコードにマッチするわけはなく、いわば「未参照」の状態を記録する値でもあります。もし、NULLになるのがまずいのであれば、NOT NULLをここでの販売明細の商品IDの定義行に追加しないといけません。NOT NULLするかしないか、これはシステム自体の実装や用途、ワークフローに応じて決められることになるでしょう。納品書の明細行を、商品未選択の状態で用意して、そこからポップアップメニューで選択するような感じのUIだと、NOT NULLでない方が都合が良いでしょう。もっとも、その場合でも、NOT NULLを指定しつつ、「商品未選択」と言う特殊な商品を用意しておくという手法もあり得ます。どちらにしても、データベース以外のところでの決め事があって、それに従って進めると言うことになります。

外部キー制約の定義で、ON DELETE CASCADE、あるいはON UPDATE CASCADEと言う記述を追加する場合もあります。ON DELETE CASCADEがあると、主キーの側のレコードが削除されると、それを外部キーで参照している側のレコードも削除されるという動作です。商品マスターと明細では設定することは多分ないでしょう。商品がなくなると、売上の記録も無くなってしまうからです。一般に、商品マスターは販売しなくなっても残しておくものかと思われます。しかしながら、納品書と販売明細ではどうでしょうか。販売明細の納品書IDは外部キーとして納品書テーブルを参照し、これによって１つの納品書レコードと、複数の販売明細のレコードが関連づけられています。納品書を作ったけど、完全に間違えたなどの場合、納品書そのものを破棄したいかもしれません。その時、関連付けられている販売明細のレコードはどうなるかと言うと、単に納品書のレコードを削除しただけなら、販売明細のレコードは残ります。それでも、納品書IDの値を再利用つまり、後日別の納品書に利用するようなことをしない限りは、単なる参照先のないレコードが残るだけです。適切なユーザインタフェースを作ってあれば、それが目に触れることはありませんが、集計等では無視するような作りが必要になります。と言うことで、納品書を消したときに、販売明細のレコードも消えてくれる方が妥当かつ明快であるとも言えます。

ON UPDATE CASCADEは、主キーの側の値を変更したら、参照している外部キーの値も変更されるという設定です。一般に主キーは変更しないという前提があるのですが、データベースにはこのようなキーがあり、ある意味で、主キーの変更までもサポートしています。もし、この設定がないとして、主キーの値を変更したらどんな問題があるでしょう。例えば、商品ID=2を5に変えたとします。主キーなので、この変更後の値は他のレコードに存在しない値であることは保証されています。しかしながら、このテーブルを参照している外部キーはまだ2のままです。そして、外部キーのあるテーブルの該当するフィールドの値を全て、2から5に変更する必要があります。複数のレコードにわたって変更をする必要があります。また、それを複数のテーブルに渡って行う必要があるかもしれません。しかしながら、ON UPDATE CASCADEにより、それを自動的に行なってくれます。

これなら、主キーは変えてもいいじゃないかと言う話もあるかもしれません。もちろん、システム全体にわたって、ON UPDATE CASCADEがきちんと機能するならいいのですが、懸念されることは、通常システムの実装においては、主キーの値をどこかに一時的に保持することはよくあることです。例えば、Webアプリケーションで、あるレコードの値を変更するようなページがあるとしたら、データベースからデータを読み込み、ページを構築はもちろん行います。そして、画面上のデータを変更したときにサーバーに送り込んで更新をデータベースに反映させないといけません。その時、主キーの値を手がかりに、修正したレコードを特定した上で、更新処理を行います。そうなれば、ページを表示して、修正する前での間に、主キーの値が変化してしまうと、更新処理での元レコードの特定ができない、つまり特定してもその値ではなくなってしまうので、多分存在しないレコードを更新しようとして、エラーになってしまうという問題があります。データベースの都合上、確かにON UPDATE CASCADEは便利なのですが、システム全体のことを考えると原則として、主キーは変更しないというルールは守るのが良いでしょう。そして、主キーを変えるような作業を頻繁に行わないようなワークフローとデータベース設計を行い、どうしても変えないといけないような場合は、誰も利用しない時間帯にメンテナンスとして実施するか、あるいはUI上の処理を禁止するような仕組みを組み込んでおくなど、運用上、あるいは実装上の工夫は必要になります。

ユーザインタフェース側で、あり得ない値を設定するような仕組みを排除し、可能な限り検証をしたデータをサーバに送り込んでいるので、外部キー制約は不要であるといった考え方もあります。もちろん、そのような状況においては、外部キー制約に引っかかることは全くないと言えるかもしれません。それでも、制約は設定すべきという意見の方が合理的です。まず、ユーザインタフェースにバグがあった時、そのバグによって整合性が崩れたデータが入力されてしまうよりも、謎のエラーで（できればそうならないのがいいのですが）止まってしまってデータ入力されない方が、データベースとしては好ましいと言えるからです。また、外部からのデータをCSVファイル等で入力するような場合には、結果的に制約の検証は事前に確実に行われているかどうかは怪しいもので、おかしなデータがやってくるなんて言うのはザラにある話です。その場合、データを検証するプログラムを通してから読み込むと言うこともあるかもしれませんが、外部キーのフィールドについては外部キー制約の制限をデータベースに与えておいて、読み込みながら検証する方がより効率的であるのではないでしょうか。と言うことで、参照制約をサボる理由はあまりなく、設定しておくことでのメリットの方が多いので、早い段階から設定はしておくべきです。

2回に渡って制約の話を行いましたが、これらの制約をうまく利用することで、データベース内のデータが整合が取れた状態をキープできます。もっとも、何を持って整合が取れているのかと言うことは、きちんと把握が必要になります。どうなって欲しいのかということを設計では考慮しますが、どうなって欲しくないのかと言うことにも気を配ると、いろいろな解答が得られます。

この場を利用して、小田嶋隆先生がお亡くなりになられたことに対して、お悔やみ申し上げます。先生との関わりは、新卒で入った日経BPでの原稿の発注先でした。いくつかの会社に発注していたのですが、後楽園のマンションにあるオフィスに緊張しながら初めて伺ったのが懐かしい思い出です。その後、移転、さらにご自宅での執筆になるくらいまでお仕事をお願いしていたのですが、原稿できるまで張り付くと言うことが常になっていて、明け方に奥様の運転で自宅に送っていただいたき、途中で焼き肉を奢ってもらったことも懐かしい思い出です。その後、私は原稿を発注していた別の会社に転職することになり私もライター業を主業務にしていたこともあったのですが、編集者として張り付いていた時に、ライターとしての働きぶりを、かなりリアルに見せていただいたことが自分にとっての大きな糧になっていました。先生ありがとうございます、安らかにお眠りください。（その頃に原稿をお願いしていた人たちが、もう何人も鬼籍に入っているなぁ。）