テーブルの外部キーの変更について

Question

データベースのテーブルを設計する時に出て来る外部キーについての質問です。
「達人に学ぶDB設計指南書」という本を読んでいるのですが、外部キーの変更に関してわからないところがあります。

社員テーブルには社員ID、社員名、年齢、部署の4つのカラムがあります。
そして、部署列が外部キーであり、部署テーブルの部署列を参照しています。
部署列は開発、人事、営業、総務の4つのレコードを持っています。
このような状況の中で、
「部署テーブルはいわば社員テーブルの「親」に当たる存在であるため、「子」の状態を気にすることなく変更することが可能です。これは人間の親子関係と同じです。子は親が存在しないと存在できませんが、子のいない親は存在しうる、ということです。」
という説明があります。
さらに、次のような記述がありました。
「それでは、親である部署テーブルのレコードが変更されたり削除されたりした場合は、子の社員テーブルはどのような影響を受けるでしょうか？
これについては、社員テーブルを作成する際に、要件に応じて動作を選ぶことが可能です。例えば、部署テーブルの開発レコードが削除された場合、社員テーブルの加藤さんや田島さんは、「親がいない子」になってしまいます。こうした「親のいない子」も合わせて削除するか、それとも削除SQLをエラーにするかを選択可能です。このあわせて削除する動作をカスケードと呼びます。
更新の場合も同じで、「親がいない子」のデータを合わせて変更するか、更新SQLをエラーにするかを選択可能です。
ただし、一番良いのはこうした厄介な問題を考えないようにテーブルを操作することです。つまり、常に子のテーブルを先に削除なり変更して、後から親のテーブルを更新していれば、こうした問題は起きないのです。」

最後の部分について質問です。
先に子のテーブルをを変更して、後から親のテーブルを変更すれば良いとありますが、例えば上のような状況で「開発」というレコードを「開発研究」と変更したいとします。
この定石に従えば、先に社員テーブルの部署カラムの「開発」レコードを「開発研究」と名前を変えればいいわけですが、外部キーの参照整合性制約によりこれはできないと思うのですが、可能なのでしょうか？
親の部署テーブルの４つのカラムに「開発研究」は存在しないのに、社員テーブルの方で「開発研究」をレコードとして用いることが可能なのでしょうか？
回答お願いします。

Answer 1

話を混ぜ返すようで恐縮ですが、外部キーについては "CRUD is dead" とかいう面白い考え方があります。

DBのCRUDでUとDは必要ないという話を聞きましたが具体的にどう実装するの？

参考まで。

Answer 2

自動車運転教本を読んいるだけではクルマの運転できなから、自動車学校で実際にクルマを運転して覚えます。SQLも書籍は読むだけではなかなか身に付きません。開発環境を用意して実際にSQLを実行された方が良いです。

maisumakun さんのコメントに補足で、
親テーブルをDELETE すれば 子テーブルも自動的に DELETE されるのが ON DELETE CASCADE です。ここの掲示板は過去ログの検索機能もありますから活用してください。
https://teratail.com/questions/48534

Answer 3

根本的に、「人間に表示するための文字列」をそのまま「外部キー」として連結に使ってしまっているのが問題です。

部署とのヒモ付けを、名前でなく（機械的に生成したような）人工キーで行っていれば、通常人工キーを入れ替えることは起きませんので、何も気にせず部署名を変更できます。

どうしても連結に使うキー自体を入れ替える必要が出た場合には、ON UPDATE CASCADEを使うのが妥当でしょう。

Comments

[退会済みユーザー]

はい、そのような内容は本にも書かれております。
何も気にせず部署名を変更できるというのは、新たなレコードを追加するということでしょうか？
また、どうしても連結に使うキー自体を入れ替えるというのは、例えば「開発」を「研究開発」に変更するということなのでしょうか？

[maisumakun]

IDで関連付けをやっておけば、ある従業員が「15番」の部署に属しているとなったときに、その15番の名前が「開発」だろうが「研究開発」だろうが、リレーションとは独立して変更できるようになる、ということです。

[退会済みユーザー]

理解できました。
私が質問にあげたような状況の場合、どうなるのでしょうか？
外部キーで「開発」となっている時に、子である社員テーブルでは「研究開発」と変更できるのでしょうか？

[maisumakun]

そのような、「存在しない値」を設定できないようにするのが外部キーですので（NULL許容の場合のNULLは例外ですが）、リレーションに使う値を変えたい場合は「別途で親を用意してつなぎ替える」もしくは「ON UPDATE CASCADEで一気に変える」しかなくて、「先に存在しない値を子の方でセットしておく」なんて芸当は通常できません。