CHECK制約で排他的サブタイプを実現する
データベースの設計を勉強すると、スーパータイプとサブタイプという考え方が出てきます。
たとえば、社員データベースを作成するとして、その属性に「社員番号」「名前」があって、正社員は「月給」を、アルバイトは「時給」を持ちたい場合。
スーパータイプとして社員テーブルを作成して、そのサブタイプに正社員テーブルと、アルバイトテーブルを作成するという設計です。
その場合のテーブル構造はこんな感じです。
基本的に、スーパータイプの主キーをサブタイプも主キーにします。
社員テーブル | スーパータイプ |
---|---|
社員番号 | 主キー |
名前 |
正社員テーブル | サブタイプ |
---|---|
社員番号 | 主キー |
月給 |
アルバイトテーブル | サブタイプ |
---|---|
社員番号 | 主キー |
時給 |
社員テーブルにはすべての社員の社員番号の登録がありますが、正社員テーブルには正社員の社員番号のみ、アルバイトテーブルにはアルバイトの社員番号のみの登録になります。
当然、正社員テーブルとアルバイトテーブルの社員番号にダブりはありません。社員番号はどちらかにしか登録できません。
この、スーパータイプの主キーがどちらか1つのサブタイプにしか登録されない状態を排他的サブタイプと言います。
しかし、普通にテーブルをデータベースに作成しただけですと、この排他的サブタイプの条件が守られるかどうかは、データを登録する側の実装にゆだねられる割合が多いです。データを登録するシステムにバグがあったりチェックを忘れられていたり、仕様から抜けたり、システムの設計者にこのデータベース設計の思想が伝わっていなければ、簡単に崩れてしまいます。
じゃあ、データベース側でなんとかできないかというのが今回のテーマです。
CHECK制約
PostgreSQLの場合しか考えていませんが。
PostgreSQLにはCHECK制約というのがありました。CHECKの内容に引っかかれば、エラーになります。
これを使って、排他的サブタイプの条件から外れそうな場合にエラーにします。
方法は
- 他のサブタイプテーブルの内容を確認する関数を作る
- 作った関数を使ってCHECK制約を作る
こんな感じになります。
作る関数はたとえば
CREATE FUNCTION アルバイトかどうか関数(integer) RETURNS boolean AS 'SELECT (SELECT 社員番号 FROM アルバイトテーブル WHERE 社員番号 = $1) IS NOT NULL' LANGUAGE 'SQL';
みたいな感じにしてみます。この関数はアルバイトテーブルに登録があれば、TRUEを返します。
これを正社員テーブルへ
CONSTRAINT 正社員に登録できるか CHECK(アルバイトかどうか関数(社員番号) = FALSE)
みたいな感じでうまい具合に使って、CHECK制約を追加してください。
こうすれば、すでにアルバイトテーブルに登録のある社員番号は、正社員テーブルには登録できなくなります。
同じような制約をアルバイトテーブルにも追加しておけば、完成ですね。
それとも何か他にうまい方法があったら教えて欲しいです。