参照整合性制約(外部キー制約)

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

img_0334

近くの川で、かもが泳いでいるのが見えたので、写真にとってみました。
9月のシルバーウィークも終わり、もうすぐ10月を迎えようとしています。今年は台風も多く日本に上陸し、いろいろと大変な天候が続いていました。地球温暖化の影響もますます大きくなってきている気がしますね。
この前、NHKで放映していたNHKスペシャル MEGA CRISIS 巨大危機~脅威と闘う者たち~ 第1集「加速する異常気象との闘い」をたまたま見たのですが、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;

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf

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

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

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_insert

上記のように、「ORA-02291: 整合性制約(BLOG.FK_BUMON)に違反しました - 親キーがありません」というエラーが出て、レコードを登録できません。

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

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_del

 この場合も、上記のように、「ORA-02292: 整合性制約(BLOG.FK_BUMON)に違反しました - 子レコードがあります」というエラーが出て、削除することはできません。

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

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

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_upd

 この場合も、やはり「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;

blog69_%e5%88%b6%e7%b4%84_%e5%86%8d%e4%bd%9c%e6%88%90

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

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

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf_100

部門マスタのBUMON_CD = ‘100’のデータを削除します。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e9%83%a8%e9%96%80del

このとき、先ほど抽出した、部門CD=100 だった社員マスタのデータを確認します。

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

blog69_%e7%a4%be%e5%93%a1%e3%83%9e%e3%82%b9%e3%82%bf%e7%a2%ba%e8%aa%8d1

 社員マスタの部門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;

blog69_%e5%88%b6%e7%b4%84_%e5%86%8d%e4%bd%9c%e6%88%902

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

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

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf_100

部門マスタのBUMON_CD = ‘100’のデータを削除します。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e9%83%a8%e9%96%80del

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

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

blog69_%e7%a4%be%e5%93%a1%e3%83%9e%e3%82%b9%e3%82%bf%e7%a2%ba%e8%aa%8d2

親表から削除した部門CD=’100’を持つ、子表のレコードが削除されたことが確認できます。
※実際に使用する場合には、子表のレコードが自動的に削除されてしまうので、運用上問題がないか注意が必要です。

このように、DBのほうでデータの整合性を保つように制御することができますが、実際のシステムではあまりこれを細かく設定したという経験は少ないのが実情です。これには賛否両論があり、実際にシステムに実装した場合に、テストデータなどの作成がいろいろと大変になるとか、レスポンスへの影響、また、整合性のエラーが起きた際のエラーメッセージをユーザ向けに変更する処理が必要になり、アプリ側での負担になる場合が多いということなどが影響しているようです。
次回は、SQL Serverで試してみたいと思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

外部表(2)(ORACLE)

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

img_0418

写真は、前回も載せましたが、富山の五箇山という世界遺産合掌造り集落に行ったときの写真です。

さて、とうとうブラジル・リオデジャネイロで行われていたパラリンピックも終わりました。オリンピック、パラリンピックとも、多くの感動を私たちに与えて頂いたと、選手の方々に本当に感謝します。
パラリンピックでは金メダルはなかったということですが、過去最高のメダル数を獲得。また、ちょっと話題は古くなりますが、先月のオリンピックでは、体操団体での悲願の金メダル、個人総合2連覇の内村選手の最後の大逆転は圧巻でしたね。伊調馨選手のレスリング4連覇は、女子個人競技で史上初めてのこと。卓球でも男子団体で銀メダル、女子団体も銅メダル、女子バドミントンダブルスの日本初の金メダル、本当に手に汗握って応援しました。柔道も男子は全階級でメダル、男子の陸上4*100メートルリレー銀メダル、競泳陣の活躍、数え上げればきりがないです。

次はいよいよ東京オリンピック、パラリンピック! 4年間という長い努力と訓練の期間、想像を絶する戦いの末に、栄光があるのでしょう。私たちも、道は違いますが、選手の方々に負けないよう、与えられた環境の中で精いっぱい頑張って行かなければと背中を押される気がします。
選手の皆さん、本当にお疲れ様でした。そして、感動をありがとう!

<本日の題材>
Oracleの外部表(2)

前回、ORACLEの外部表についてORACLE_LOADER型を取り上げましたが、今回は、ORACLE_DATAPUMP型というものを試してみたいと思います。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルにデータを書き込んだり、読込んだりすることができます。

例)
商品売上という、商品コード毎、年度毎の各月の売上金額を集計したテーブルがあり、そこに、2014年、2015年、2016年のデータが登録されているとします。
テーブルの定義は以下のようになっています。
DESC 商品売上

desc_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

データとしてはこんな感じ。

%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a%e3%83%87%e3%83%bc%e3%82%bf

 年度毎の件数は、
SELECT 年度, COUNT(*) FROM 商品売上
GROUP BY 年度
ORDER BY 年度;

%e5%b9%b4%e5%ba%a6%e3%81%94%e3%81%a8%e4%bb%b6%e6%95%b0

サンプルデータなので件数は少ないです。
それで、今年度(2016)のデータだけ残して、過去の年度のデータは、テーブルとしては持たないでダンプファイルに保存し、必要なときには外部表としてアクセスするようにしたいと思います。

まず、2014年のデータの外部表を作成し、外部表のダンプファイルにデータを登録します。

CREATE TABLE SYO_URIAGE_2014
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2014.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2014';

syo_uriage_2014

 次に、同様にして、2015年のデータの外部表を作成します。

CREATE TABLE SYO_URIAGE_2015
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2015.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2015';

syo_uriage_2015

実際に、ダンプファイルが指定のフォルダに作成されていることを確認してみます。今回ディレクトリに指定した「temp_dir」は、前回のブログで作成したように、「C:\temp」フォルダになりますので、エクスプローラで確認すると、

temp_dir

確かに、「URIAGE_2014.DMP」「URIAGE_2015.DMP」ファイルが作成されていました。

それぞれ作成した外部表のデータ件数を確認してみます。
SELECT COUNT(*) FROM SYO_URIAGE_2014;

syo_uri_2014_count

SELECT COUNT(*) FROM SYO_URIAGE_2015;

syo_uri_2015_count

元の商品売上テーブルからは、過去の年度のデータは削除し、2016年度のデータのみを残すことにします。

DELETE FROM 商品売上
WHERE 年度 < '2016';

del_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

 処理の上で、2016年度のデータを見ればよい場合は「商品売上」テーブルに対してSELECTを実施しますが、過去のデータも見たい場合には、外部表を使うことで検索できます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM 商品売上
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2015
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2014
 WHERE 商品CD = 'A0001'
ORDER BY 年度;

%e5%a4%96%e9%83%a8%e8%a1%a8_union

 また、上記でユーザ「BLOG」によって作成された外部ファイル(ダンプファイル)を、別のユーザで使用するようなこともできます。

今回、「BLOG2」というユーザを作成し、「temp_dir」をBLOG2ユーザからも読み書きできるようにします。

CREATE USER BLOG2
IDENTIFIED BY< password>    -- パスワードを設定します
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

GRANT READ, WRITE ON DIRECTORY temp_dir TO BLOG2;

grant_read_write_blog2

作成したユーザー「BLOG2」でログインし、上記で作成した2015年度の商品売上データのダンプファイル(uriage_2015.dmp)を元にBLOG2ユーザ用の外部表「SYO_URIAGE2_2015」を作成してみます。

  CREATE TABLE SYO_URIAGE2_2015
   ("商品CD" VARCHAR2(20 BYTE),
              "年度" VARCHAR2(4 BYTE),
              "売上4月" NUMBER(12,0),
              "売上5月" NUMBER(12,0),
              "売上6月" NUMBER(12,0),
              "売上7月" NUMBER(12,0),
              "売上8月" NUMBER(12,0),
              "売上9月" NUMBER(12,0),
              "売上10月" NUMBER(12,0),
              "売上11月" NUMBER(12,0),
              "売上12月" NUMBER(12,0),
              "売上1月" NUMBER(12,0),
              "売上2月" NUMBER(12,0),
              "売上3月" NUMBER(12,0)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY TEMP_DIR
      LOCATION
       ( 'uriage_2015.dmp'
       )
    );

 %e5%a4%96%e9%83%a8%e8%a1%a8%e4%bd%9c%e6%88%902

作成した外部表を検索してみます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
 FROM SYO_URIAGE2_2015
ORDER BY 商品CD;

select_%e5%a4%96%e9%83%a8%e8%a1%a82

 データの検索ができました。
上記のように、一度作成した外部ファイル(ダンプファイル)は、別のユーザや別のデータベース環境に移動してそこで使用することも可能なんですね。
さらに、PREPROCESSOR句を使用すると、外部ファイルを圧縮したファイルに対して解凍しながら使用するということも可能なようです。

(※)注意点として、DATAPUMP(expdp)で作成したdmpファイルは外部表のダンプファイルとしては使用できないようです。

 今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村