2017/12/07

テーブルに登録するデータを制限する機能である制約があります。制限したいデータを登録しようとした時にORACLEが自動的にエラーを返してくれるので、使い方次第では便利な機能です。
その中でも、外部キー制約(参照制約・参照整合性制約)について、本記事では解説します。
sponsored link
外部キー制約(参照制約・参照整合性制約)とは
外部キー制約(参照制約・参照整合性制約)とは、あるテーブルの項目値が、参照先のテーブルに存在しない値が登録されるのを許可しないという制約です。関連するテーブル間の整合性を保つための制約ですね。
例えば、受注履歴テーブルの取引先コードに、取引先マスタに存在しない値は登録できないというものです。
実際にそのようなデータを登録しようとするとSQLエラーになります。
ただし、NULLの登録は可能です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- テーブル1(参照先テーブル) A --- 001 002 -- テーブル2(参照元テーブル) B C --- - -- ★外部キー制約(テーブル2のBは、テーブル1のAに存在するデータに限る) -- 参照先テーブルに存在する値をデータ登録 INSERT INTO test_table2 VALUES ('001','z'); ⇒ OK -- 参照先テーブルに存在しない値をデータ登録 INSERT INTO test_table2 VALUES ('003','w'); ⇒ ORA-02291: 整合性制約(XXXXX.YYYYY)に違反しました - 親キーがありません XXXXX:スキーマ名 YYYYY:外部キー制約名 -- NULLをデータ登録 INSERT INTO test_table2 VALUES (NULL,'x'); ⇒ OK |
設定方法
外部キー制約の設定方法は、テーブル作成時や変更時にCONSTRAINT句を指定します。
制約名は、省略可能です。省略時はORACLEが自動的に命名します。
文法(テーブル作成時)
1 2 3 4 5 6 |
CREATE TABLE 参照元テーブル名 ~~ CONSTRAINT 外部キー制約名 FOREIGN KEY (参照元項目名) REFERENCES 参照先テーブル名 (参照先項目名) オプション ; |
文法(テーブル変更時)
1 2 3 4 5 |
ALTER TABLE 参照元テーブル名 ADD CONSTRAINT 外部キー制約名 FOREIGN KEY (参照元項目名) REFERENCES 参照先テーブル名 (参照先項目名) オプション ; |
オプション
オプションは、参照先テーブルのデータ削除時に、参照元テーブルに存在した時の動作を指定します。
1 2 3 4 5 6 7 8 9 10 |
指定なし ⇒ ORA-02292: 整合性制約(XXXXX.YYYYY)に違反しました - 子レコードがあります XXXXX:スキーマ名 YYYYY:参照整合性制約名 ON DELETE SET NULL ⇒ 参照元テーブルのデータをNULLに更新する ON DELETE CASCADE ⇒ 参照元テーブルのレコードを削除する |
削除方法
外部キー制約の削除方法は、テーブル変更時にDROP CONSTRAINTを指定します。
また、参照元テーブルのテーブル削除(DROP TABLE)時には、外部キー制約も合わせて削除されます。
参照先テーブルのテーブル削除(DROP TABLE)時には、オプション(CASCADE CONSTRAINTS)をつけることで外部キー制約も合わせて削除されます。オプション指定しない場合は、エラー(ORA-02449)となります。
文法
1 2 3 |
ALTER TABLE 参照元テーブル名 DROP CONSTRAINT 参照整合性制約名 ; |
文法(参照先テーブルの削除)
1 2 3 4 |
DROP TABLE 参照先テーブル名 CASCADE CONSTRAINTS; ※参照先テーブル名のテーブル削除でオプション(CASCADE CONSTRAINTS)をつけなかった場合 ⇒ ORA-02449: 表の一意キーまたは主キーが外部キーに参照されています。 |
注意点
注意点を以下にまとめました。
- 参照先項目には、主キーまたは一意キーが張られている必要がある参照先項目には、主キーまたは一意キーが張られている必要がある
- 参照先テーブルのトランケートはNG
- 参照先テーブルのテーブル削除はオプション(CASCADE CONSTRAINTS)をつける必要がある
- 参照元項目へのNULLの登録はOK
まとめ
関連するテーブル間の整合性を保つための制約である外部キー制約について見てきました。
データ登録時や削除時に、自動で整合性チェックをしてくれるので、不整合なデータが絶対に登録されないというメリットがあります。
しかし、その代償として、参照先テーブルのトランケートが出来ないなどの使いにくさもあります。(外部キー制約を事前に削除しておく等の方法はありますが)
外部キーを使用すべきか、またはアプリ側でデータ更新時のチェックを徹底するべきか、よく検討した上で作成するとよいでしょう。
関連