10月4日 (火)  連鎖参照整合性制約

外部キー設定のオプション?で一側のあるレコードを削除した時、多側に同じキーとなる値を持つレコードがあったら、一側の同じキーを持つレコードも削除したり、一側の値が更新されたら多側の値も更新されるとか言う便利な奴のことなんですが、SQLサーバでは、ってほかにはAccessしか知らんのですが、それらのテーブル操作に対してSET TO NULL とか SET TO DEFAULT とかがあるらしいです。

ttp://msdn.microsoft.com/ja-jp/library/ms186973.aspx

で、早速試してみたのですが、SET TO NULL の方は多側の該当するカラムにNULLを許可しておいたら、一側でその値を持つレコードを削除したら多側の値もNULLになっていたのですが、SET TO DEFAULT がいくら試してもエラーと言うか、削除する事が出来ない。先に示したURLには SET TO DEFAULT の説明として

他テーブルの既存の行にある外部キーで参照されるキーを含む行を削除すると、参照される行の外部キーを構成するすべての値が既定値に設定されるように指定します。この制約を適用するには、参照先のテーブルのすべての外部キー列にデフォルト定義が必要です。NULL 値を許容する列に明示的な既定値が設定されていない場合、NULL 値がその列の暗黙的な既定値になります。ON DELETE SET DEFAULT によって設定される NULL 以外の値は、外部キー制約の有効性を保つため、主テーブルの対応する値にする必要があります。

と書いてあるのですが、「参照する」とか「参照される」と言う表現がどうもわかりにくい。まぁ頭が悪いから仕方が無いのだろうが。リモートとかホストとかローカルとかと同じくらい悩んでしまう。笑

で、何度か読むうちにふと気づいた。既定値を設定するのは多側では無く、一側のキーとなる列に対してでは無いかと。

で、さっそく一側のキー列にデフォルト値を設定しておいて、別のキーを持つレコードを削除してやると、多側にあった同じキーを持つレコードの値が一側で設定したデフォルト値に更新されていた。なんとかこれでスッキリはしたのだが、良く考えたらこれらの動作は参照整合性になっているのだろうか。SET TO NULL の結果発生した多側のNULL値は一側には存在しないんですよ。まぁ、NULLと言うのは有るのか無いのかワカラン特殊な値なので「例外的」にOKなのかも知れませんが…。それにSET TO DEFAULT にしても、一側でキーになる値と言うのは主キーな訳で、それにデフォルト値があると言うのも変な話だと…。

まぁ、アホな頭ではこれらの動作の利点としては、多側テーブルのキー列に既定値やNULLをキー列にもつレコードを見つけたとき、「あぁ、マスタレコードを誰か削除したんやなぁ」と後から解ることくらいしか思い浮かばんが、何か良い使い道はあるのだろうか。またはトリガなどと上手く組み合わせて使うとハッピーになれるのだろうか。