こんなスキーマ設計を見ました。
オブジェクト指向のプログラミングでは、クラスを継承してサブクラスを作ることはよくあります。そして、アプリケーションではデータを複数のサブクラスに分けて扱っているのに、それを格納するデータベースでそのデータ構造をうまく表現できないなんて悩みにはわりとよく直面します。
一部RDBが持っているテーブル継承機能がそれっぽいんですが、親テーブルと子テーブルで主キーの一意性制約がかけられないなどいまいち実用性に欠けます。
では親クラス（例えば【従業員】）で主キーの一意性を保証したまま複数のサブクラス（例えば【正社員】【アルバイト】）を格納するにはどんなスキーマを設計すればいいか。
これには定石が2つあります*1。
- シングルテーブル継承 … 「従業員」テーブルを作り、正社員用のカラムもアルバイト用のカラムもそこに持たせます。使わないカラムにはNULL。
- クラステーブル継承 … 「従業員」テーブルで主キーを管理し、「正社員」「アルバイト」テーブルをそれに従属させます。
前者の欠点はとにかくスマートじゃないこと。不要なカラムが多くなるから構造は理解しづらくなるし、パフォーマンスにも響くし、あるクラスの設計を変更するとき他のクラスとのカラム名衝突を考慮しないといけないなんて何世紀前のプログラミングですかって。
後者の欠点は整合性。「正社員でもアルバイトでもない従業員」「正社員でもアルバイトでもある従業員」なんていう矛盾したデータを簡単に作れてしまいます。
後者、クラステーブル継承のスマートさを生かしつつ整合性を保証することを考えてみましょう。
CHECK制約で実現する
ここで、最初に紹介した記事の方法。この方法、ちょっとすごい表技を使っています。
こういう一般的なクラステーブル継承を作ってから、
CHECK制約で他テーブルに同じ主キーが含まれないか調べているのです。
現行のデータベースではたぶん、CHECK制約の中で副問い合わせを許しているものはありません。SQL99では定義されているのですが、やはり制約を常に保証する実装が難しすぎるんでしょう。
ところが、副問い合わせをストアド関数として切り出してしまうと、CHECK制約の中にも書けてしまうのです。それを利用した限りなく裏技くさい表技。この方法を使ってCHECK制約に他テーブルへの参照を含めてしまうと、制約が保証されなくなります。この例に限ってはうまく動いてしまいそうに見えますが。
とはいえもう一つ問題。
「正社員でもアルバイトでもある従業員」は禁止できましたが、「正社員でもアルバイトでもない従業員」がまだ禁止できていません。
何か他にうまい方法があったら教えて欲しいです。
とのことなので僕なりの解を。
サブクラス用の主キーを独立させる
正社員テーブル・アルバイトテーブルにそれぞれ主キーとして【正社員ID】・【アルバイトID】を設けます。
そのカラムは従業員テーブル側にもあります。
これらのカラムについて、サブクラステーブルと親クラステーブルの間で相互に外部キー制約をかけるのです。
一つ前の記事で説明した、遅延制約を使っての相互外部キー制約です。
これで、正社員・アルバイトテーブルそれぞれの行の存在を従業員テーブルで制御できるようになりました。
そこで「正社員でもアルバイトでもある従業員」「正社員でもアルバイトでもない従業員」を禁止します。
ADD TABLE 従業員 CONSTRAINT 従業員は正社員かアルバイトどちらか CHECK(CASE 正社員ID IS NULL THEN 0 ELSE 1 END +CASE アルバイトID IS NULL THEN 0 ELSE 1 END = 1);
従業員テーブルで、正社員IDとアルバイトID、どちらかがNULLでどちらかがNULLでないとのCHECK制約をつけます*2。
以上の手順で、整合性を保証したクラステーブル継承が実現できました。
正社員ID・アルバイトIDというのは実際の書類には存在しないキーなので、従業員IDと実際には同じ数字ということにした方がいいケースも多いでしょう。そういうときはさらに
ADD TABLE 従業員 CONSTRAINT アルバイトIDは従業員ID CHECK(アルバイトID=従業員ID); ADD TABLE 従業員 CONSTRAINT 正社員IDは従業員ID CHECK(正社員ID=従業員ID);
CHECK制約は結果がNULLだとパスするので、こういう制約はNULLチェックなしで記述できます。
