質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

Q&A

解決済

1回答

1622閲覧

一意キー設定の考え方を教えて下さい

combined_co2

総合スコア11

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

0グッド

0クリップ

投稿2019/06/03 01:54

編集2019/06/03 01:58

発生している問題・エラーメッセージ


イメージ説明

イメージ説明

イメージ説明

①まず以下の思考順で以下のテーブルを作りました。  1.とりあえずサロゲートキーを作る(色々なサイトで、サロゲートキーは付けろと書かれていたから)  2.もし行を削除することになった場合、同じ番号は再利用出来ないので、サロゲートキー以外にもUNIQUEなキーを作る。   (部署コードと従業員コード)  3.ググった結果、子テーブルの外部キーは、親テーブルの主キーを参照するのが普通のようなので、   従業員テーブルの外部キーは、部署マスタのサロゲートキーを参照する。 ②次に「このケースだと、部署テーブルのレコードが削除された場合、従業員テーブルが参照する部署IDが消えてしまい、  部署IDは再利用出来ないため、二度と使えなくなるのでは?」と思い、以下のテーブルに変更しました。  ※従業員テーブルが、部署IDでなく、部署コードを参照するようにした。 ③次に「部署コードが一意制約を持っており、従業員テーブルも部署コードを参照するので、部署IDはいらないのでは?」  と考え、以下のように変更しました。

結果的に、サロゲートキーが消えてしまいました。
そうすると、サロゲートキーは付けろ!という一般論?に反する結果になってしまい、①に戻って思考がループしています。
どうすればよいでしょうか・・・

補足情報(FW/ツールのバージョンなど)

DBは、PostgreSQL10です。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

②次に「このケースだと、部署テーブルのレコードが削除された場合、従業員テーブルが参照する部署IDが消えてしまい、

 部署IDは再利用出来ないため、二度と使えなくなるのでは?」と思い、以下のテーブルに変更しました。
※従業員テーブルが、部署IDでなく、部署コードを参照するようにした。

この判断は間違っています。
外部キーで参照されている以上「部署テーブルの該当レコードの削除」は不可能(制約違反になる)ですし、そうあるべきです。(部署コードを参照していても同じですが)

削除をしたいのであれば、先に従業員テーブルの参照先を別の部署IDに変更しておく必要があるはずです。

サロゲートキーが必要か否か、というのは、単純なテーブル設計上の問題と言うよりも(例えば自然キーが複合主キーになる場合など)サロゲートキーを使うことでクエリをシンプルできるなど、アプリ側での扱いを単純にできる事にメリットがあるかどうか、の問題です。(なのでだいたい付ける)

投稿2019/06/03 02:12

gentaro

総合スコア8949

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

combined_co2

2019/06/03 02:17

ありがとうございます。 ①ON UPDATE と ON DELETE を RESTRICT に設定し、サロゲートキーは維持する。  ということでよいのでしょうか? ②クエリをシンプルにする為ということは、今回のように部署コードと従業員コードの1カラムだけで  レコードを特定出来る場合は、サロゲートキーなしでもたぶん大丈夫。ということでしょうか。
gentaro

2019/06/03 02:29 編集

①は、NO ACTIONだとしても結局はトランザクション内で整合性を取る必要があるでしょうから、何を疑問に思っておられるのかわかりません。(最終的に目指しているものは同じ) ②は「サロゲートキーは付けろ!という一般論」と認識されているとおり、これは一般論としてそうしておいた方が良い場合が多い、ということであって、必須ではありません。 なので、大丈夫か?と言われると「あなたがそうする必要があると思って、そうしたいなら、それでいい」という答えになります。 個人的にはサロゲートキーを付けるメリットとして、「従業員コード」だとか「部署コード」というレベルであればだいたい一意なのかな?とは思いますが、例えばそのテーブルに過去に退職した従業員のレコードも存在し(退職フラグ等で管理されており)、「従業員コード」は使い回されている、というケースもあると思いますので、そのテーブルのデータを扱う時にいちいちそれが一意なのか?という事を気にせずに「ID」で統一されている方が扱いやすいとは思いますが。
combined_co2

2019/06/03 02:33

>結局はトランザクション内で整合性を取る必要がある >従業員コードの使い回し この考え方が抜けていました。サロゲートキーは必ず付けるようにします。 ありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問