レコード削除でのテーブル修正

質問

初めて、データベースを作成するのですが、
基本的な部分でつまずいています。

テーブルのレコード削除において、
そのテーブルの主キーを外部キーに使用している他のテーブルの修正に関して
教えて頂きたいのですが？

例えば、テーブルが2つ(Table-A、Table-B)あり、
フィールドとして、
Table-A　(1)販売員コード(主キー)　(2)名前　　　 　　(3)年齢
Table-B　(1)顧客コード(主キー)　　(2)販売員コード(外部キー)　(3)注文日　　(4)商品コード(外部キー)

があったとします。

この時、Table-Aの販売員コード　0015番が辞めたので、削除する場合、

1.通常、0015番は、永久欠番にするのでしょうか、それとも新しい販売員に
　割り当てることもあるのでしょうか？
2.もし、後者である場合、Table-Bの(2)販売員コードフィールドの0015番のすべてのデータは、
　元の販売員と名前が違う訳ですから、どう対応したらよいのでしょう？

永久欠番であれば、まだ分かるのですが、一般に各テーブルの主キーは一度決めたら、
変更しないものなのか、変更もありえるのか教えて欲しいのです。

初心者なので、質問内容が質問になっていないかもしれませんが、宜しくお願いします。

回答No.3

t_ohta · ベストアンサー

＞　もし自動採番にしていた場合、後述されてます
＞　＞ちゃんと外部キー制約を付けたらRDBMSが削除を受け付けてくれない
＞　ならば、
＞　番号の範囲を使い切ってしまっても1番に戻って採番が始まらないということになりますか？

シーケンスの設定次第ですが、番号を使い切ってしまったら１番に戻って番号を発行してしまいますので、ダブった番号を登録しようとしてしまいます。
販売員コードが主キーに設定されているなら、ユニーク値しか入れられませんので、INSERTでエラーが出ると思います。

＞　＞外部キーとして参照されている場合、Table-Bに販売員コードが使われている限り
＞　＞Table-Aのレコードは削除できなくするのが普通です。
＞　これは、永久欠番という考え方で宜しいでしょうか？
＞　＞Table-Aにステータス情報を持つカラムを用意しておき、販売員が辞めたときはステータスを「退職」にして管理するのが普通です。
＞　これも永久欠番という考え方で宜しいでしょうか？

どちらも永久にDBにデータを残すと言う事です。
別の人に同じ番号を発行しないという意味では永久欠番と言えるでしょう。

Table-Aは販売員のマスターテーブルですからデータを消さないという考え方をします。
DELETEでデータ削除することを「物理削除」と呼びます。
永久にデータが見られなくなるため、Table-Bから参照しようとした時エラーになってしまうので問題があります。
この場合、Table-Aにステータスカラムを用意し在籍中は「1」とし、退職したらデータを「0」にUPDATEすると言った方法を取り、販売員を検索する場合などはステータスが「1」のレコードだけを検索するようにして、プログラム上で見えなくします。
こういった方法を「論理削除」と呼びます。
これによって、データの不整合が発生を防げます。

お礼

お時間を頂き、大変有難う御座いました。

どうも経験がないため、一般的にどうなのか
考えこんでしまいました。
大変参考になりました。

回答No.4

maiko0318

＞1レコードのみを削除した場合はどうなるのでしょうか？

２モードあります。
１）Table-Aから0015番を消したらTable-Bの0015番全てが消える設定。
２）Table-Bに0015番が存在する限り、Table-Aの0015番が消せなくする設定。
です。

お礼

お時間を頂き、大変有難う御座いました。

この様な設定方法もあるんだなと、
今後の参考になりました。

この度は大変有難う御座いました。

回答No.2

t_ohta

販売員コードの採番ルールはどうなっていますか？
社員番号など会社が管理する番号なら永久欠番になるでしょうが、シーケンスを使って自動採番する場合、番号の範囲を使い切ってしまったら1番に戻って採番が始まります。（余程頻繁に追加しないとありえませんが）

また、外部キーとして参照されている場合、Table-Bに販売員コードが使われている限りTable-Aのレコードは削除できなくするのが普通です。（ちゃんと外部キー制約を付けたらRDBMSが削除を受け付けてくれない）
Table-Aにステータス情報を持つカラムを用意しておき、販売員が辞めたときはステータスを「退職」にして管理するのが普通です。

補足

ご回答大変有難う御座います。

初めてですので、自動採番という単語勉強になりました。
現在、自動採番にはしていません。
もし自動採番にしていた場合、後述されてます
＞ちゃんと外部キー制約を付けたらRDBMSが削除を受け付けてくれない
ならば、
番号の範囲を使い切ってしまっても1番に戻って採番が始まらないということになりますか？

＞外部キーとして参照されている場合、Table-Bに販売員コードが使われている限り
＞Table-Aのレコードは削除できなくするのが普通です。
これは、永久欠番という考え方で宜しいでしょうか？

＞Table-Aにステータス情報を持つカラムを用意しておき、販売員が辞めたときはステータスを「退職」にして管理するのが普通です。
これも永久欠番という考え方で宜しいでしょうか？

回答No.1

maiko0318

外部キーで連結していれば、Table-Aを消した瞬間にTable-Bも消えてしまいます。
もちろん、何か別の要因でTable-Bが消せない時は、Table-Aも消せません。

補足

ご回答大変有難う御座います。

消せない場合を除いて、
＞外部キーで連結していれば、Table-Aを消した瞬間にTable-Bも消えてしまいます。
これはテーブル（Table-A）全部を消した時、連結しているテーブルも消えるということですね。

だとしますと、1レコードのみを削除した場合はどうなるのでしょうか？