ITコーディネータのシュウです。
近くの川で、かもが泳いでいるのが見えたので、写真にとってみました。
9月のシルバーウィークも終わり、もうすぐ10月を迎えようとしています。今年は台風も多く日本に上陸し、いろいろと大変な天候が続いていました。地球温暖化の影響もますます大きくなってきている気がしますね。
この前、NHKで放映していたＮＨＫスペシャル ＭＥＧＡ ＣＲＩＳＩＳ 巨大危機～脅威と闘う者たち～ 第１集「加速する異常気象との闘い」をたまたま見たのですが、21世紀末には平均気温が今より4℃上昇し、最高気温が東京で43・0度、名古屋では44.5度にもなるという予測が出ていました。また、巨大積乱雲(スーパーセル)も頻繁に発生し、東京都の最大雨量も1日あたり現在100mm程度なのが、310mmまで増加し、大雨のときには地下街は水没してしまうかもしれないというような、とてつもない状況をこのままでは迎えてしまうという話しです。
21世紀末と言えば、80数年後なので、私たちの孫や曾孫が生きている世代です。いよいよ、環境問題は待ったなしの状況に来ている気がしますね。このような脅威と如何に向き合い、温暖化を抑制していくのか、科学の英知の結集と世界的な連携が必要だということを改めて感じるこの頃です。
＜本日の題材＞
参照整合性制約（外部キー制約）
リレーショナルデータベースには、整合性制約と言って、データの整合性を保つための制約を設定することができ、主キー制約、一意キー制約、NOT NULL制約、CHECK制約、参照整合性制約などがあります。
今回は、参照整合性制約（外部キー制約）について取り上げてみたいと思います。参照整合性制約とは、関連付けられた表の共通する列の関係が保たれていることをDB側で保証するもので、簡単にいえば、参照先のテーブルにデータが存在しないようなデータの入力を許可しないということになります。
よく例として挙げられるのが、ORACLEにあらかじめ用意されているEMP表（従業員表）とDEPT表（部門表）で、EMP表の部門番号(DEPTNO)は、DEPT表の部門番号(DEPTNO)に存在するデータしか登録できないというものです。
このとき、DEPT表を親表、EMP表を子表、DEPTNOを外部キーと言います。そして、DEPT表のDEPTNOは主キー、もしくは一意キーである必要があります。
この参照整合性制約の設定は、基本的にテーブル作成時に設定しますが、後から制約を追加することも可能です。
例）
それでは、例として、社員マスタと部門マスタを作成し、部門マスタの部門CDを社員マスタから参照するかたちの設定をしてみます。（ORACLE）
まず、部門マスタを作成します。
CREATE TABLE BUMON_M(
BUMON_CD VARCHAR2(3)
, BUMON_NAME VARCHAR2(20)
, CONSTRAINT PK_BUMON PRIMARY KEY (BUMON_CD)
);
社員マスタを作成します。
CREATE TABLE SYAIN_M(
SYAIN_CD VARCHAR2(10)
, SYAIN_NAME VARCHAR2(20)
, BUMON_CD VARCHAR2(3)
, POSITION VARCHAR2(40)
, CONSTRAINT PK_SYAIN PRIMARY KEY (SYAIN_CD)
, CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
REFERENCES BUMON_M (BUMON_CD)
);
上記の、7～8行目の CONSTRAINT .. FOREIGN KEY .. REFERENCES ..という表記部分が参照整合性制約の設定箇所になります。
部門マスタ（BUMON_M）に何件かデータを登録し、現在以下の部門が存在しているとします。
SELECT * FROM BUMON_M
ORDER BY BUMON_CD;
この状況で、部門マスタに存在しない部門CD「500」で社員を登録しようとしてみると、
INSERT INTO SYAIN_M VALUES('10311', 'メンバーＫ', '500', '一般社員');
上記のように、「ORA-02291: 整合性制約(BLOG.FK_BUMON)に違反しました - 親キーがありません」というエラーが出て、レコードを登録できません。
また、各部門CDを持つ社員データを社員マスタに登録した状態で、部門マスタから既存の部門CDのレコードを削除しようとしてみます。
DELETE FROM BUMON_M
WHERE BUMON_CD = '100';
この場合も、上記のように、「ORA-02292: 整合性制約(BLOG.FK_BUMON)に違反しました - 子レコードがあります」というエラーが出て、削除することはできません。
それでは、子表にデータが存在している親表のキーの値を更新してみます。
UPDATE BUMON_M SET
BUMON_CD = '500'
WHERE BUMON_CD = '300';
この場合も、やはり「ORA-02292: 整合性制約(BLOG.FK_BUMON)に違反しました - 子レコードがあります」というエラーが出て、更新することはできません。
以上のように、参照整合性制約（外部キー制約）のデフォルトの設定は、NO ACTION と言われるもので、参照先のデータの更新や削除を禁止するかたちになります。
（※親表のテーブルをDROPしようとしてもエラーになります。）
そのため、どうしても親表のデータを更新したいときには、更新する値と一致する子表の部門CDの値を一旦親表に存在する違う値に更新してから、親表のキー値を更新するというような手順が必要になります。
ただ、オプションの設定として、「ON DELETE SET NULL」「ON DELETE CASCADE」というものがあります。それぞれ、どういう動作になるかというと、
・ON DELETE SET NULL
これは、親表のキー（部門CD）の値を削除すると、その値と同じ値を持っていた子表のほうの部門CDは NULL に更新されるというものです。
・ON DELETE CASCADE
これは、親表のキー（部門CD）の値を削除すると、その値と同じ値を持っていた子表のほうのレコード自体が削除されるというものです。
試してみます。
参照整合性制約のオプションを変更するため、現在の制約を削除し、オプションを設定した制約を追加します。
ALTER TABLE SYAIN_M DROP CONSTRAINT FK_BUMON;
ALTER TABLE SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
REFERENCES BUMON_M (BUMON_CD) ON DELETE SET NULL;
まず、社員表から、部門CD=’100’のデータを確認します。
SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;
部門マスタのBUMON_CD = ‘100’のデータを削除します。
DELETE FROM BUMON_M
WHERE BUMON_CD = '100';
このとき、先ほど抽出した、部門CD=100 だった社員マスタのデータを確認します。
SELECT * FROM SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;
社員マスタの部門CDが「100」であったデータが、NULL に変わっていることが確認できます。
一旦元に戻します。
ROLLBACK;
次に、参照整合性制約のオプションを、ON DELETE CASCADE に変更します。
ALTER TABLE SYAIN_M DROP CONSTRAINT FK_BUMON;
ALTER TABLE SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
REFERENCES BUMON_M (BUMON_CD) ON DELETE CASCADE;
再度、社員表から、部門CD=’100’のデータを確認します。
SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;
部門マスタのBUMON_CD = ‘100’のデータを削除します。
DELETE FROM BUMON_M
WHERE BUMON_CD = '100';
このとき、先ほどの社員マスタのデータを確認します。
SELECT * FROM SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;
親表から削除した部門CD=’100’を持つ、子表のレコードが削除されたことが確認できます。
※実際に使用する場合には、子表のレコードが自動的に削除されてしまうので、運用上問題がないか注意が必要です。
このように、DBのほうでデータの整合性を保つように制御することができますが、実際のシステムではあまりこれを細かく設定したという経験は少ないのが実情です。これには賛否両論があり、実際にシステムに実装した場合に、テストデータなどの作成がいろいろと大変になるとか、レスポンスへの影響、また、整合性のエラーが起きた際のエラーメッセージをユーザ向けに変更する処理が必要になり、アプリ側での負担になる場合が多いということなどが影響しているようです。
次回は、SQL Serverで試してみたいと思います。
今日は以上まで
にほんブログ村
