ITコーディネータのシュウです。

知人が松本城に行ったときに撮ってきた写真を頂きました。
ちょっと曇りの天気なのが残念ですが、天守閣が堀と一緒にきれいに写っています。松本城の天守は国宝で、その周りにある石垣や堀も国の史跡に指定されているそうです。天守の建造年は、戦国時代～江戸時代初期の頃だそうで、NHK大河ドラマで放映中の「真田丸」の主人公　真田信繁（幸村）が活躍した時代に重なるようです。
真田丸のほうも関ヶ原の戦いも終わり、徐々にクライマックスに向かっていきますね。ちなみに、「真田丸」とは、真田信繁が、大阪城に入城した後、大坂冬の陣の際に、大阪城の最弱部であったとされるところの近辺に築いた土づくりの出城のことのようです。今後、大河ドラマで放映されると思われる真田丸での戦いを、是非見てみたいです。

＜本日の題材＞
参照整合性制約（SQL Server）

前回、参照整合性制約（外部キー制約）について、ORACLEでの例を上げて記事をアップしました。今回は、SQL Serverで試してみたいと思います。

例）
前回ORACLEで試したものと同様のテーブルをSQL Serverで作成します。社員マスタと部門マスタを作成し、部門マスタの部門CDを社員マスタから参照するかたちの設定をしてみます。
まず、部門マスタを作成します。

CREATE TABLE dbo.BUMON_M(
BUMON_CD VARCHAR(3)
, BUMON_NAME VARCHAR(20)
, CONSTRAINT PK_BUMON PRIMARY KEY (BUMON_CD)
);

次に、社員マスタを作成します。

CREATE TABLE dbo.SYAIN_M(
 SYAIN_CD VARCHAR(10)
, SYAIN_NAME VARCHAR(20)
, BUMON_CD VARCHAR(3) DEFAULT ‘ ‘
, POSITION VARCHAR(40)
, CONSTRAINT PK_SYAIN PRIMARY KEY (SYAIN_CD)
, CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES dbo.BUMON_M (BUMON_CD)
);

上記の、7～8行目の CONSTRAINT .. FOREIGN KEY .. REFERENCES ..という表記部分が参照整合性制約の設定箇所で、ORACLEでの記述と基本同じです。

部門マスタ（BUMON_M）に何件かデータを登録し、現在以下の部門が存在しているとします。

SELECT * FROM dbo.BUMON_M
ORDER BY BUMON_CD;

この状況で、部門マスタに存在しない部門CD「500」で社員を登録しようとしてみると、

INSERT INTO dbo.SYAIN_M VALUES('10311', 'メンバーＫ', '500', '一般社員');

 上記のように、SQL Serverの場合も、ORACLE同様エラーが出てレコードを登録できません。SQL Serverでのエラーメッセージは、「メッセージ 547、レベル 16、状態 0、行 38　INSERT ステートメントは FOREIGN KEY 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.BUMON_M", column 'BUMON_CD' です。」となります。

また、各部門CDを持つ社員データを社員マスタに登録した状態で、部門マスタから既存の部門CDのレコードを削除しようとしてみます。

DELETE FROM dbo.BUMON_M
WHERE BUMON_CD = '100';

 この場合も、上記のように、「メッセージ 547、レベル 16、状態 0、行 41　DELETE ステートメントは REFERENCE 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.SYAIN_M", column 'BUMON_CD' です。」というエラーが出て、削除することはできません。

それでは、子表にデータが存在している親表のキーの値を更新してみます。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '300';

この場合も、やはり「メッセージ 547、レベル 16、状態 0、行 45　UPDATE ステートメントは REFERENCE 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.SYAIN_M", column 'BUMON_CD' です。」というエラーが出て、更新することはできません。

以上のように、参照整合性制約（外部キー制約）のデフォルトの設定は、NO ACTION と言われるもので、参照先のデータの更新や削除を禁止するかたちになります。

SQL Serverでも、オプションの設定として、下記のようなものがあります。
・ON DELETE { CASCADE | SET NULL | SET DEFAULT }
・ON UPDATE { CASCADE | SET NULL | SET DEFAULT }

「ON DELETE CASCADE」「ON DELETE SET NULL」については、前回、ORACLEで動作を確認しましたので、今回は省略し、それ以外のオプションについて確認してみます。

・ON UPDATE { CASCADE | SET NULL | SET DEFAULT }
ORACLEにはなかったオプションです。動作を確認してみます。

参照整合性制約のオプションを変更するため、現在の制約を削除し、オプションを設定した制約を追加します。このとき、ON DELETE CASCADE に加えて ON UPDATE CASCADE も設定します。

ALTER TABLE dbo.SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE dbo.SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M(BUMON_CD)
        ON DELETE CASCADE
        ON UPDATE CASCADE;

 まず、社員表から、部門CD=’100’のデータを確認します。

SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;

部門マスタのBUMON_CD = ‘100’のデータを'500'に更新します。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '100';

このとき、先ほどの社員マスタのデータを確認します。

SELECT * FROM dbo.SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

社員マスタの部門CDが「100」であったデータが、「500」に変わっていることが確認できます。

次に、参照整合性制約のオプションを、ON UPDATE SET NULL に変更します。

ALTER TABLE dbo.SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE dbo.SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M(BUMON_CD)
        ON DELETE CASCADE
        ON UPDATE SET NULL;

 先ほど変更した部門マスタのBUMON_CD = ‘500’のデータを、元の’100’に変更します。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '100'
WHERE BUMON_CD = '500';

このとき、先ほど部門CD「500」になっていた社員マスタのデータを確認します。

SELECT * FROM dbo.SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

先ほど、部門マスタ側で変更した部門CDをもつ社員マスタの部門CDは、NULL に変更されていることが確認できました。

※ON UPDATE SET DEFAULT、ON DELETE SET DEFAULT については、親表の対応する行が削除、更新されると、子表の外部キーを構成する値にデフォルト値がセットされるという認識でしたが、テストしてみたところ、思ったような動作にならなかったため、今回は省略します。

以上から、SQL Serverの場合には、ON UPDATE … という更新時の対応のオプションがあるのが確認できました。

今日は以上まで

にほんブログ村

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で試してみたいと思います。

今日は以上まで

にほんブログ村