DBの具象テーブル継承時に、具象先が単一であることを保証する仕組み

投稿者: Anonymous

例えば、StudentクラスとTeacherクラスが、Userクラスを継承しているとします。
それをDBテーブルで表現すると、以下のようになるかと思います。(具象テーブル継承)

userテーブル

  • id (※主キー)
  • name(氏名)

studentテーブル

  • user_id(※外部キー)
  • student_no(学籍番号)
  • class_room(所属クラス)

teacherテーブル

  • user_id(※外部キー)
  • office_room(職員用の居室)

このとき、userの具象先(studentteacher)が単一であることを保証する仕組みは、存在しますでしょうか?
あるuserレコードに対して、studentteacherの両方にレコードが存在できてしまう場合を懸念しています。

解決

ご質問のクラステーブル継承を使った場合でも、CHECK制約を使って継承先を一意に特定することができます。

  1. StudentテーブルとteacherテーブルにそれぞれSTUDENT_IDTEACHER_IDを主キー(PK)として設定します。
  2. それをuserテーブルの該当カラムに外部キー(FK)として追加します。
  3. 最後にuserテーブルのCHECK制約として下記を設定します。
    (下記はOracleの構文例です)
CONSTRAINT CHK_TYPE CHECK (case when STUDENT_ID is NULL then 0 else 1 end + case when TEACHER_ID is NULL then 0 else 1 end = 1)

このCHECK制約は1つ目のcase文でSTUDENT_IDの有無を、2つ目のcase文でTEACHER_IDの有無をフラグ化して、どちらか片方しかない場合のみチェックが通るようにしています。

このテーブル設計により、StudentテーブルのSTUDENT_IDは一意であり、teacherテーブルのTEACHER_IDも一意であることが保証されます。
userテーブルの上記IDをStudentテーブルとteacherテーブルに外部結合する設計で、「あるuserレコードに対して、studentteacherの両方にレコードが存在できてしまう場合」はなくなります。

ただしこの状態では「userテーブルに同一のStudentレコード(またはteacherレコード)が重複できてしまう場合」が防げませんので、必要に応じて重複チェックなどを追加してください。

参考: データベースでクラス継承を表現する


Oracleで検証に使ったコード:

CREATE TABLE STUDENT
(
    STUDENT_ID                     NUMBER NOT NULL,
    CONSTRAINT PK_STUDENT PRIMARY KEY (STUDENT_ID) USING INDEX
);
/
CREATE TABLE TEACHER
(
    TEACHER_ID                     NUMBER NOT NULL,
    CONSTRAINT PK_TEACHER PRIMARY KEY (TEACHER_ID) USING INDEX
);
/
CREATE TABLE USER
(
    USER_ID                        NUMBER,
    STUDENT_ID                     NUMBER,
    TEACHER_ID                     NUMBER,
    CONSTRAINT CHK_TYPE CHECK (case when STUDENT_ID is NULL then 0 else 1 end +  case when TEACHER_ID is NULL then 0 else 1 end = 1),
    CONSTRAINT FK_STUDENT FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT (STUDENT_ID) ON DELETE CASCADE,
    CONSTRAINT FK_TEACHER FOREIGN KEY (TEACHER_ID) REFERENCES TEACHER (TEACHER_ID) ON DELETE CASCADE
);

@keitaro_so さんのご賢察の通り、私の回答は継承元の制約がありませんので参考にされる方はご留意願います。

各種テーブルで相互に外部キーを貼ることによって、片方にしか存在しないキーができないように監視することができます。
ただしこの仕組みは遅延制約(DEFERRABLE)やトリガなどを使って一瞬だけ外部キー制約を回避しないと実現できません。
(新規レコードを追加する時、teacherテーブルに入れようとしてもuserテーブルがないので制約違反になり、userテーブルに入れようとしてもまた制約違反になります)

CHECK制約はSqliteを含めて大抵のRDBで実装されていますが、遅延制約はPostgresqlとOracleくらいしか見つからなかったので回答に含めませんでした。
参考: http://cs.hatenablog.jp/entry/2013/07/12/235046

回答者: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *