ORA-02292とORA-02266が発生した時の原因調査方法

「ORA-02292: 整合性制約×××に違反しました。 ORA-02266: 表には有効な外部キーによって参照される一意キー/主キーが含まれています。」何れも FOREIGN KEY （外部キー） に関わるデータの整合性を壊さないようにするために出力されるエラーです。子テーブルからキー参照されているにもかかわらず、DELETE、UPDATE、TRUNCATE要求がされたためです。

まずは検証環境

以下のよう親子関係のテーブルがあったとします。

上記のFOREIGN KEY （外部キー）作成DDLは以下の通り

alter table OY_PREFECTURE add constraint FK_REGION_REGION_ID foreign key (REGIN_ID) references OY_REGION(REGION_ID)
;

親子の紐付けが切れてしまうような更新を試みると ORA-02266 や ORA-02292 が発生する

SQL> truncate table OY_REGION;
truncate table OY_REGION
               *
行1でエラーが発生しました。:
ORA-02266:
表には有効な外部キーによって参照される一意キー/主キーが含まれています。


SQL> update OY_REGION set REGION_ID=10 where REGION_ID =1;
update OY_REGION set REGION_ID=10 where REGION_ID =1
*
行1でエラーが発生しました。:
ORA-02292: 整合性制約(SOONI.FK_REGION_REGION_ID)に違反しました -
子レコードがあります


SQL> delete from OY_REGION t where t.REGION_ID =1;
delete from OY_REGION t where t.REGION_ID =1
*
行1でエラーが発生しました。:
ORA-02292: 整合性制約(SOONI.FK_REGION_REGION_ID)に違反しました -
子レコードがあります


SQL>

子テーブルから参照されているのにTRUNCATEやUPDATE、DELETEで関係性が壊れてしまうので当然エラーとなるわけですが、一瞬どのテーブルが原因でエラーになっているのか戸惑ってしまいがちです。そんな時は以下SQLで原因となっている子テーブル（FK）を見つける事ができます。

FOREIGN KEY （外部キー）を見つけるSQL

SELECT
 c.OWNER
,c.CONSTRAINT_NAME
,c.CONSTRAINT_TYPE
,c.TABLE_NAME
,c.status
FROM dba_constraints c
inner join dba_constraints t
on c.R_CONSTRAINT_NAME = t.CONSTRAINT_NAME
where t.TABLE_NAME = 'OY_REGION' and t.OWNER ='SOONI' 
;

SQL実行例

SQL> SELECT
  2   c.OWNER
  3  ,c.CONSTRAINT_NAME
  4  ,c.CONSTRAINT_TYPE
  5  ,c.TABLE_NAME
  6  ,c.status
  7  FROM dba_constraints c
  8  inner join dba_constraints t
  9  on c.R_CONSTRAINT_NAME = t.CONSTRAINT_NAME
 10  where t.TABLE_NAME = 'OY_REGION' and t.OWNER ='SOONI'
 11  ;

   OWNER        CONSTRAINT_NAME    CONSTRAINT_TYPE       TABLE_NAME     STATUS
________ ______________________ __________________ ________________ __________
SOONI    FK_REGION_REGION_ID    R                  OY_PREFECTURE    ENABLED

SQL>

関連する項目を出力するSQL

関連する項目はDBA_CONS_COLUMNSを参照する事でわかります

select n.OWNER,n.CONSTRAINT_NAME,n.TABLE_NAME,n.POSITION,n.COLUMN_NAME
from dba_CONS_COLUMNS n where n.CONSTRAINT_NAME='FK_REGION_REGION_ID' and n.OWNER ='SOONI'
order by n.POSITION
;

SQL実行例

SQL> select n.OWNER,n.CONSTRAINT_NAME,n.TABLE_NAME,n.POSITION,n.COLUMN_NAME
  2  from dba_CONS_COLUMNS n where n.CONSTRAINT_NAME='FK_REGION_REGION_ID' and n.OWNER ='SOONI'
  3  order by n.POSITION
  4  ;

   OWNER        CONSTRAINT_NAME       TABLE_NAME    POSITION    COLUMN_NAME
________ ______________________ ________________ ___________ ______________
SOONI    FK_REGION_REGION_ID    OY_PREFECTURE              1 REGIN_ID

SQL>

子テーブルの外部キーを無効にするSQL

これで親テーブルをTRUNCATEできます

ALTER TABLE OY_PREFECTURE DISABLE CONSTRAINT FK_REGION_REGION_ID
;

しかしながら、このような参照整合性の制約が作られていた状態で親テーブルをtruncateするって事は子テーブルもtruncateする事になるんじゃないかなと、、

有効にするにはこちら

ALTER TABLE OY_PREFECTURE ENABLE CONSTRAINT FK_REGION_REGION_ID
;

意外と、FKを嫌う（採用をしない）お客様が多いです。直接SQLをたたけるお客様に多い気がします。「うちは付けないルールだから」と検討の余地なく不採用となるケースもありました、、データの整合性を担保するしくみなのでなるべく採用をすすめたいです。FKが入っているためにテストケースが減るとか、実装コストが下がるはずなので、、