2017/12/07
テーブルに登録するデータを制限する機能である制約の一つである外部キー制約（参照制約・参照整合性制約）について、ORACLEで使用した際に発生するエラーをまとめました。
sponsored link
参照先に存在しないデータを参照元に登録（ORA-02291）
参照先テーブルに存在しないデータを参照元テーブルに登録しようとすると、ORA-02291のエラーとなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- テーブル１（参照先テーブル） A --- 001 002 -- テーブル２（参照元テーブル） B --- -- ★外部キー制約（テーブル２のBは、テーブル１のAに存在するデータに限る） -- 参照先に存在しないデータを参照元に登録 INSERT INTO test_table2 VALUES ('003'); ORA-02291: 整合性制約(XXXXX.YYYYY)に違反しました - 親キーがありません XXXXX：スキーマ名 YYYYY：参照整合性制約名 |
参照元に存在するデータを参照先から削除or更新（ORA-02292）
参照元テーブルに存在するデータを、参照先テーブルから削除、または該当項目を更新しようとすると、ORA-02292のエラーとなります。
ただし、削除については、外部キー設定時に【ON DELETE SET NULL】や【ON DELETE CASCADE】のオプションを付けなかった場合に限ります。
ちなみに、参照先のデータ削除時に、【ON DELETE SET NULL】を付けて外部キーを設定していた場合は、紐つく参照元テーブルのデータがNULLに更新されます。【ON DELETE CASCADE】を付けて外部キーを作成していた場合は、紐つく参照元テーブルのレコードが削除されます。
更新については、どの外部キー設定時のオプションの有無や種類に関わらず、エラーになります。
|
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 |
-- テーブル１（参照先テーブル） A --- 001 002 -- テーブル２（参照元テーブル） B --- 001 -- ★外部キー制約（テーブル２のBは、テーブル１のAに存在するデータに限る） -- 参照元に存在するデータを参照先から削除 DELETE FROM test_table1 WHERE a='001'; ORA-02292: 整合性制約(XXXXX.YYYYY)に違反しました - 子レコードがあります XXXXX：スキーマ名 YYYYY：参照整合性制約名 -- 参照元に存在するデータを参照先から更新 UPDATE test_table1 SET a='003' WHERE a='001'; ORA-02292: 整合性制約(XXXXX.YYYYY)に違反しました - 子レコードがあります |
参照先に存在しないデータが参照元に存在する状態で外部キー設定（ORA-02298）
参照先テーブルに存在しないデータが参照元テーブルに存在する状態で、外部キーを設定しようとすると、ORA-02291のエラーとなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- テーブル１（参照先テーブル） A --- 001 002 -- テーブル２（参照元テーブル） B --- 003 -- 参照先に存在しないデータが参照元に存在する状態で外部キー設定 ALTER TABLE test_table2 ADD CONSTRAINT fk_test_table2 FOREIGN KEY (b) REFERENCES test_table1 (a) ; ORA-02298: (XXXXX.YYYYY)を有効にできません - 親キーが見つかりません。 XXXXX：スキーマ名 YYYYY：参照整合性制約名 |
参照先の主キー・一意キー項目以外への外部キー設定（ORA-02270）
参照先テーブルの主キー項目・一意キー項目以外への外部キーを設定しようとすると、ORA-02270のエラーとなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- テーブル１（参照先テーブル） -- 項目aに主キーが設定されている A Z --- - 001 0 002 0 -- テーブル２（参照元テーブル） B --- -- 参照先の主キー・一意キー項目以外への外部キー設定 ALTER TABLE test_table2 ADD CONSTRAINT fk_test_table2 FOREIGN KEY (b) REFERENCES test_table1 (z) ; ORA-02270: この列リストに対して一致する一意キーまたは主キーが存在しません。 |
参照先のトランケート（ORA-02266）
参照先テーブルにトランケートをしようとすると、ORA-02266のエラーとなります。
ちなみに、参照元テーブルのトランケートは問題なく実行できます。
|
1 2 3 4 5 6 |
-- ★外部キー制約（テーブル２のBは、テーブル１のAに存在するデータに限る） -- 参照先のトランケート TRUNCATE TABLE test_table1; ORA-02266: 表には使用可能な外部キーによって参照される一意キー/主キーが含まれています。 |
参照先のテーブル削除（ORA-02449）
参照先テーブルの削除（DROP TABLE）をしようとすると、ORA-02449のエラーとなります。
テーブル削除時にオプション【CASCADE CONSTRAINTS】を付けることで制約と合わせて削除することが出来ます。
ちなみに、参照元テーブルの削除は問題なく実行できます。制約と合わせて削除されます。
|
1 2 3 4 5 6 |
-- ★外部キー制約（テーブル２のBは、テーブル１のAに存在するデータに限る） -- 参照先のトランケート DROP TABLE test_table1; ORA-02449: 表の一意キーまたは主キーが外部キーに参照されています。 |
まとめ
外部キー制約に関わるエラーを見てきました。
外部キー制約として当然のエラーもありますが、参照先テーブルのトランケートやテーブル削除のエラーは、考慮が漏れてしまうことが意外と多いです。
気をつけましょう。
関連
