Sql

外部キー制約を無効にせずに、トランザクション内で参照整合性を簡単に解除するにはどうすればよいですか？



How Can I Break Referential Integrity Briefly



解決：

必要なのは「遅延制約」です。

2種類の延期可能な制約、「INITIALLYIMMEDIATE」と「INITIALLYDEFERRED」から選択して、デフォルトの動作を駆動できます。データベースがデフォルトですべてのステートメントの後に制約をチェックするか、デフォルトで最後に制約のみをチェックするかを選択できます。トランザクションの。




Chiよりも遅い回答でしたが、SOで回答が見つかるように、コードサンプルを含めるとよいと感じました。

Chiが答えたように、延期可能な制約がこれを可能にします。



SQL>ドロップテーブルt;テーブルが削除されました。 SQL> create table T（ID番号2、parent_ID番号null 3、名前varchar2（40）not null 4、制約T_PK主キー（ID）5、制約T_HIREARCHY_FK外部キー（parent_ID）6参照T（ID）最初は即時に延期可能） ;テーブルが作成されました。 SQL> T値に挿入（1、null、 'Big Boss'）; 1行が作成されました。 SQL> T値に挿入（2、1、 'Worker Bee'）; 1行が作成されました。 SQL>コミット;コミットが完了しました。 SQL>-最初は即時なので、次のステートメントは失敗します。SQL> update T 2 set ID = 1000 3 where ID = 1; 1行目のT *エラーを更新します。ORA-02292：整合性制約（S.T_HIREARCHY_FK）に違反しました-子レコードでSQLが見つかりました>制約をすべて延期しました。制約セット。 SQL> update T 2 set ID = 1000 3 where ID = 1; 1行が更新されました。 SQL> update T 2 set parent_ID = 1000 3 where parent_ID = 1; 1行が更新されました。 SQL>コミット;コミットが完了しました。 SQL> select * from T; ID PARENT_ID NAME ---------- ---------- --------------------------- ------------- 1000 Big Boss 2 1000 Worker Bee SQL>-そのトランザクション中にすべて延期された制約を設定しますSQL>-トランザクションがコミットされると、次のSQL>-ステートメントは失敗しますSQL> update T 2 set ID = 1 3 where ID = 1000; 1行目の更新T *エラー：ORA-02292：整合性制約S.T_HIREARCHY_FK）に違反しました-子レコードが見つかりました
延期可能性は制約の作成時に定義され、後で変更することはできないと思いますが、参照を見つけることができませんでした。デフォルトは延期できません。延期可能な制約に変更するには、一度ドロップして制約を追加する必要があります。 （適切にスケジュールされ、制御されているなど）
 
SQL>ドロップテーブルt;テーブルが削除されました。 SQL> create table T（ID番号2、parent_ID番号null 3、名前varchar2（40）not null 4、制約T_PK主キー（ID）5、制約T_HIREARCHY_FK外部キー（parent_ID）6参照T（ID））;テーブルが作成されました。 SQL>テーブルの変更Tドロップ制約T_HIREARCHY_FK;テーブルが変更されました。 SQL>テーブルの変更T制約の追加T_HIREARCHY_FK外部キー（parent_ID）2は、最初に延期されたT（ID）の延期を参照します。テーブルが変更されました。 
このようなシナリオでの一般的なアドバイスは、延期可能な制約を採用することです。ただし、これらの状況は、ほとんどの場合、アプリケーションロジックまたはデータモデルの障害であると思います。たとえば、子レコードと親レコードを同じトランザクションに挿入すると、次の2つのステートメントとして実行すると問題が発生する可能性があります。
 
私のテストデータ：
 
 
 
SQL> select * from t23 order by id、parent_id 2 / ID PARENT_ID NAME ---------- ---------- -------------- ---------------- 110親1111親2210110子0220111子1221111子2222111子36行が選択されています。 SQL>
物事を行うための間違った方法：
 
SQL> t23（id、parent_id、name）値（444、333、 'new child'）に挿入2 / t23（id、parent_id、name）値（444、333、 'new child'）に挿入*行にエラー1：ORA-02291：整合性制約（APC.T23_T23_FK）に違反しました-親キーが見つかりませんSQL> t23（id、parent_id、name）値（333、null、 '新しい親'）に挿入2/1行が作成されました。 SQL>
ただし、Oracleは、親レコードと子レコードを挿入できるマルチテーブルINSERTsynatxをサポートしています。 同じステートメントで 、したがって、延期可能な制約の必要性を排除します。
 
SQL>ロールバック2 /ロールバックが完了しました。 SQL> 2つすべてをt23（id、parent_id、name）に挿入3つの値（child_id、parent_id、child_name）4をt23（id、name）に挿入5つの値（parent_id、parent_name）6 333をparent_idとして選択7、 'new parent' as parent_name 8、444 as child_id 9、 'new child' as child_name 10 from dual 11 / 2rowscreated。 SQL>
現在の状況も同様です。親レコードの主キーを更新したいが、子レコードが存在するために更新できません。また、親キーがないため、子レコードを更新できません。キャッチ22：
 
SQL> update t23 2 set id = 555 3 where id = 111 4 / update t23 *エラー1行目：ORA-02292：整合性制約（APC.T23_T23_FK）に違反しています-子レコードが見つかりましたSQL> update t23 2 set parent_id = 555 3ここで、parent_id = 111 4 / update t23 * 1行目のエラー：ORA-02291：整合性制約（APC.T23_T23_FK）に違反しています-親キーが見つかりませんSQL>
もう一度、解決策は単一のステートメントでそれを行うことです：
 
SQL> update t23 2 set id = decode（id、111、555、id）3、parent_id = decode（parent_id、111、555、parent_id）4ここで、id = 1115またはparent_id = 1116/4行が更新されました。 SQL> select * from t23 order by id、parent_id 2 / ID PARENT_ID NAME ---------- ---------- -------------- ---------------- 110親1210110子0220555子1222555子2222555子3333新しい親444333新しい子555親28行が選択されました。 SQL>
UPDATEステートメントの構文は少し不格好ですが、通常は不格好です。重要なのは、主キー列を頻繁に更新する必要がないということです。実際、不変性は「主キー」の特徴の1つであるため、実際に更新する必要はまったくありません。そうする必要があるのは、データモデルの失敗です。このような失敗を回避する1つの方法は、合成（代理）主キーを使用し、一意の制約を使用して自然（別名ビジネス）キーの一意性を強制することです。
 
では、なぜOracleは延期可能な制約を提供するのでしょうか。これらは、データの移行やデータの一括アップロードを行うときに役立ちます。これにより、テーブルをステージングせずにデータベース内のデータをクレンジングできます。通常のアプリケーションタスクでは、これらは実際には必要ありません。