ある条件下でユニークな制約を実現する

PostgreSQL

設計としてどうなの？　という気もしますが、たとえばアプリケーション上はキーとなるカラムがあるがテーブルの設計上は同じ値で複数行の挿入ができるようにしたい。でも、そのキーの値はある条件下ではユニークであることを保証したい場合。
具体的な例で言うと、

カラム名 制約
シーケンス番号 主キー
登録番号 NOT NULL
開始日 NOT NULL
完了日

みたいなテーブル設計にして、アプリケーションは最初にこのテーブルに登録番号と開始日を挿入する。次に更新完了日がNULLの行を抽出して処理を行ったあとに完了日を更新するという動作を想定します。
この時に、更新完了日がNULLである行だけを抽出したら登録番号はユニークであって欲しいとします。
これをPostgreSQLで実現するためには、

カラム名 制約
シーケンス番号 主キー
登録番号 NOT NULL
開始日 NOT NULL
完了日
フラグ

という風にフラグというカラムを足して、これにUNIQUE(登録番号,フラグ)のUNIQUE制約とCHECK(フラグ = TRUE AND 完了日 IS NULL OR フラグ IS NULL)のCHECK制約をつけます。（フラグ IS NULLの時には完了日はNOT NULLにしたかったが、うまくいかず。CHECK制約ではNULLは制限されないから？）
今回はフラグはboolean型ですが、これで完了日がNULLの場合はフラグがTRUEであるという実装*1をすれば、間接的ですが、完了日がNULLである行（フラグがTRUEの行）*2だけ抽出した場合の登録番号のユニークが保証できます。
これはPostgreSQLが標準SQLに準拠し、UNIQUE制約でもNULLは複数格納できるため、逆にUNIQUE制約を有効にしたい場合のみフラグに値をいれるということです。
https://www.postgresql.jp/document/9.3/html/ddl-constraints.html

*1:つまり、完了日を更新する時は同時にフラグをNULLにしなければならない。挿入時はフラグの初期値をTRUEにしておけばいい。

*2:だから、もう完了日に関係無くフラグがTRUEの行が処理対象だという仕様に変えるのが正しいかも

CHECK制約で排他的サブタイプを実現する

PostgreSQL

データベースの設計を勉強すると、スーパータイプとサブタイプという考え方が出てきます。
たとえば、社員データベースを作成するとして、その属性に「社員番号」「名前」があって、正社員は「月給」を、アルバイトは「時給」を持ちたい場合。
スーパータイプとして社員テーブルを作成して、そのサブタイプに正社員テーブルと、アルバイトテーブルを作成するという設計です。
その場合のテーブル構造はこんな感じです。
基本的に、スーパータイプの主キーをサブタイプも主キーにします。

社員テーブル スーパータイプ
社員番号 主キー
名前
正社員テーブル サブタイプ
社員番号 主キー
月給
アルバイトテーブル サブタイプ
社員番号 主キー
時給

社員テーブルにはすべての社員の社員番号の登録がありますが、正社員テーブルには正社員の社員番号のみ、アルバイトテーブルにはアルバイトの社員番号のみの登録になります。
当然、正社員テーブルとアルバイトテーブルの社員番号にダブりはありません。社員番号はどちらかにしか登録できません。
この、スーパータイプの主キーがどちらか１つのサブタイプにしか登録されない状態を排他的サブタイプと言います。
しかし、普通にテーブルをデータベースに作成しただけですと、この排他的サブタイプの条件が守られるかどうかは、データを登録する側の実装にゆだねられる割合が多いです。データを登録するシステムにバグがあったりチェックを忘れられていたり、仕様から抜けたり、システムの設計者にこのデータベース設計の思想が伝わっていなければ、簡単に崩れてしまいます。
じゃあ、データベース側でなんとかできないかというのが今回のテーマです。

CHECK制約

PostgreSQLの場合しか考えていませんが。
PostgreSQLにはCHECK制約というのがありました。CHECKの内容に引っかかれば、エラーになります。
これを使って、排他的サブタイプの条件から外れそうな場合にエラーにします。
方法は

  1. 他のサブタイプテーブルの内容を確認する関数を作る
  2. 作った関数を使ってCHECK制約を作る

こんな感じになります。
作る関数はたとえば

CREATE FUNCTION アルバイトかどうか関数(integer)
RETURNS boolean AS
'SELECT (SELECT 社員番号 FROM アルバイトテーブル WHERE 社員番号 = $1) IS NOT NULL'
LANGUAGE 'SQL';

みたいな感じにしてみます。この関数はアルバイトテーブルに登録があれば、TRUEを返します。
これを正社員テーブルへ

CONSTRAINT 正社員に登録できるか CHECK(アルバイトかどうか関数(社員番号) = FALSE)

みたいな感じでうまい具合に使って、CHECK制約を追加してください。
こうすれば、すでにアルバイトテーブルに登録のある社員番号は、正社員テーブルには登録できなくなります。
同じような制約をアルバイトテーブルにも追加しておけば、完成ですね。


それとも何か他にうまい方法があったら教えて欲しいです。

車に乗る時、お尻が廻るから乗りやすい

カーメイト くるっと回って乗り降りらくらく くるりんシート ブラック CD5

カーメイト くるっと回って乗り降りらくらく くるりんシート ブラック CD5