74355


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

`user`テーブル

  * id （※主キー）
  * name（氏名）
  * ...

`student`テーブル

  * user_id（※外部キー）
  * student_no（学籍番号）
  * class_room（所属クラス）
  * ...

`teacher`テーブル

  * user_id（※外部キー）
  * office_room（職員用の居室）
  * ...

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


CC BY-SA 4.0
2021-03-01T10:27:52.207
74342
2021-03-03T11:25:36.417
2021-03-03T08:07:06.777
36365
36365
question
database
DBの具象テーブル継承時に、具象先が単一であることを保証する仕組み


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

  1. `Student`テーブルと`teacher`テーブルにそれぞれ`STUDENT_ID`と`TEACHER_ID`を主キー(PK)として設定します。
  2. それを`user`テーブルの該当カラムに外部キー(FK)として追加します。
  3. 最後に`user`テーブルのCHECK制約として下記を設定します。  
(下記はOracleの構文例です)

[code]

    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)
    
[/code]

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

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

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

参考: [データベースでクラス継承を表現する](http://cs.hatenablog.jp/entry/2013/07/13/010318)

* * *

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

[code]

    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
    );
    
[/code]

* * *

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

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

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


CC BY-SA 4.0
2021-03-02T05:19:49.277
74355
2021-03-03T11:25:36.417
2021-03-03T11:25:36.417
9820
9820
74342
answer