sql - 親キーがありません - 外部キー制約 削除




外部キー制約を無効にすることなく、トランザクション内で簡単に参照整合性を解除できますか? (4)

私は3列のテーブルを持っています:

ID, PARENT_ID, NAME

PARENT_IDは、同じ表のIDとの外部キー関係があります。 この表は階層をモデル化したものです。

レコードのIDが変更されることがありID 。 レコードのIDを更新し、従属レコードのPARENT_IDを新しいIDを指すように更新できるようにしID

問題は、レコードのIDを更新しようとすると、整合性が損なわれ、即座に失敗するということです。

新しいIDで新しいレコードを挿入し、子を更新してから古いレコードを削除することができたことはわかっていますが、私がそうした場合、多くのトリガーがうまくいきません。

一時的に外部キーを無効にすることなく、子を更新するという約束をもって親を一時的に更新する方法はありますか(コミット時に失敗することは明らかです)?


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

Chiが答えたとき、延期可能な制約がこれを可能にする。

SQL> drop table t;

Table dropped.

SQL> create table T (ID number
  2      , parent_ID number null
  3      , name varchar2(40) not null
  4      , constraint T_PK primary key (ID)
  5      , constraint T_HIREARCHY_FK foreign key (parent_ID)
  6          references T(ID) deferrable initially immediate);

Table created.

SQL> insert into T values (1, null, 'Big Boss');

1 row created.

SQL> insert into T values (2, 1, 'Worker Bee');

1 row created.

SQL> commit;

Commit complete.

SQL> -- Since initially immediate, the following statement will fail:
SQL> update T
  2  set ID = 1000
  3  where ID = 1;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint (S.T_HIREARCHY_FK) violated - child record found


SQL> set constraints all deferred;

Constraint set.

SQL> update T
  2  set ID = 1000
  3  where ID = 1;

1 row updated.

SQL> update T
  2  set parent_ID = 1000
  3  where parent_ID = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from T;

        ID  PARENT_ID NAME
---------- ---------- ----------------------------------------
      1000            Big Boss
         2       1000 Worker Bee

SQL> -- set constraints all deferred during that transaction
SQL> -- and the transaction has commited, the next
SQL> -- statement will fail
SQL> update T
  2  set ID = 1
  3  where ID = 1000;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint S.T_HIREARCHY_FK) violated - child record found

私は信じますが、参照を見つけることができませんでした。遅延性は制約の作成時に定義され、後で変更することはできません。 デフォルトは延期されません。 遅延可能な制約に変更するには、1回のドロップを行い、制約を追加する必要があります。 (適切にスケジュールされ、制御され、等)

SQL> drop table t;

Table dropped.

SQL> create table T (ID number
  2      , parent_ID number null
  3      , name varchar2(40) not null
  4      , constraint T_PK primary key (ID)
  5      , constraint T_HIREARCHY_FK foreign key (parent_ID)
  6          references T(ID));

Table created.

SQL> alter table T drop constraint T_HIREARCHY_FK;

Table altered.

SQL> alter table T add constraint T_HIREARCHY_FK foreign key (parent_ID)
  2      references T(ID) deferrable initially deferred;

Table altered.

あなたが望むのは、「 遅延可能な制約 」です。

2つのタイプの遅延可能な制約、「INITIALLY IMMEDIATE」と「INITIALLY DEFERRED」を選択して、デフォルトの動作を実行することができます。データベースがすべての文の後で制約をチェックするかどうか、その取引の


これがOracle以外のデータベースであれば、 ON UPDATE CASCADEを使用して外部キーを宣言できます。 次に、親のidを変更すると、その変更を子のparent_idに原子的に伝播します。

残念ながら、Oracleではカスケード削除は実装されてますがカスケード更新実装されていません

(この回答は、実際にはあなたの問題を解決するものではないので、情報提供のみを目的としています。)


サロゲートキーの使用に関する推奨事項は優れていますが、IMOです。

より一般的には、このテーブルの問題は、主キーが欠けていることです。 主キーは3つのものでなければならないことを思い出してください。

  1. ユニーク
  2. nullでない
  3. 変わらない

私がよく慣れているデータベースは(1)と(2)を強制しますが、私は彼らが(3)を強制するとは思わないが、それは残念です。 そして、それが突き合わせであなたを蹴ってくれます。あなたが "主キー"を変更した場合、そのキーフィールドへのすべての参照を追い払い、整合性を壊さないようにするために同等の変更を加えなければなりません。 他の人が言っているように、解決策は真の主キー(固有で非nullで、変更されないもの)を持つことです。

これらすべての小さなルールには理由があります。 これは、主キー規則の「不変」部分を理解する絶好の機会です。

共有して楽しんでください。





referential-integrity