[Oracle]外部参照整合制約(外部キー) foreign key の挙動

お約束

  • 外部キーは 主キー or 一意キー のついたカラムにしか付けれません。
  • on update cascade オプションは Oracle では使えません。

とりあえず A, B, C テーブルを作成

create table A (
  ID number(1,0),
  primary key(ID)
);

create table B (
  ID number(1,0),
  constraint FK_B foreign key(ID) references A(ID)
);

create table C (
  ID number(1,0),
  constraint FK_C foreign key(ID) references A(ID) on delete cascade enable
);

レコード追加

SQL> insert into A values(1);

1行が作成されました。

SQL> insert into B values(1);

1行が作成されました。

SQL> insert into C values(1);

1行が作成されました。

SQL> insert into C values(0);
ORA-02291: 整合性制約(ELSIS2.SYS_C004619)に違反しました - 親キーがありません

-- 制約を外せば追加できる

SQL> alter table c drop constraint FK_C;
表が変更されました。

SQL> insert into C values(0);
1行が作成されました。

SQL> alter table C add constraint FK_C foreign key(ID) references A(ID) on delete cascade enable;
1でエラーが発生しました。:
ORA-02298: (STUDY.FK_C)を検証できません - 親キーが見つかりません。
-- 外部キーが参照する親キーが無いじゃないか、とoracleが有頂天になる。

SQL> delete from C where id = 0;
1行が削除されました。

SQL> alter table C add constraint FK_C foreign key(ID) references A(ID) on delete cascade enable;
表が変更されました。

SQL> insert into C values(0);
insert into C values(0)
*
1でエラーが発生しました。:
ORA-02291: 整合性制約(STUDY.FK_C)に違反しました - 親キーがありません

親キーが存在しない場合はレコード追加できない。

あとから制約を変更することは可能(alter ~ drop で削除して、alter ~ add で追加し直す)

レコード削除

SQL> delete from A where ID = 1;
ORA-02292: 整合性制約(ELSIS2.SYS_C004618)に違反しました - 子レコードがあります

子レコードが存在する場合は削除できない。ここの子レコードとは on delete cascade がついてない テーブルB のレコードを指している。

SQL> delete from B where ID = 1;

1行が削除されました。

SQL> delete from A where ID = 1;

1行が削除されました。

SQL> select * from C;

レコードが選択されませんでした。

B ⇒ A の順に消せば A のレコードを削除できる。C のレコードは A のレコード削除時に連鎖削除されるため消えてしまうので select すると無くなっている。

レコード更新

SQL> update A set ID = 2 where ID = 1;
ORA-02292: 整合性制約(ELSIS2.SYS_C004618)に違反しました - 子レコードがあります

外部キーを張られているカラムの値は変更できない。

SQL> insert into A values(1);

1行が作成されました。

SQL> insert into A values(2);

1行が作成されました。

SQL> insert into B values(2);

1行が作成されました。

SQL> update B set ID = 1 where ID = 2;

1行が更新されました。

でも外部キーを貼ってる側は変更できる。

SQL> alter table B disable constraint FK_B;

表が変更されました。

SQL> update A set ID = 9 where ID = 1;

1行が更新されました。

SQL> alter table B enable constraint FK_B;
ORA-02298: (ELSIS2.FK_B)を検証できません - 親キーが見つかりません。

SQL> update B set ID = 9 where ID = 1;

1行が更新されました。

SQL> alter table B enable constraint FK_B;

表が変更されました。

制約を一時的に解除することで変更できる。

ただし整合性が合わないと再び制約を有効にできない。

また、on update cascade オプションを使用出来るDBでは制約を外さなくても更新できる。

後始末

drop table A;
drop table B;
drop table C;

遊んだら片付けよう…drop table時も制約に違反しないようにしないとエラーになる。 ※drop table にも cascade constraints があるってツッコミ入ってた…ありがとうございます。

Share
関連記事