【DB】移行時のサロゲートキー対応とは
はじめに
サロゲートキーは、データベースのテーブルで使用される一意の識別子です。通常、これは自動的に生成される連番やUUIDといった値です。サロゲートキーは、データベース内のレコードを一意に識別するために使用されます。
移行時の問題点
もし移行先のデータベースで参照先のレコードの順序やIDが異なっている場合、単純に外部キーの値をそのまま移植すると、本来の参照先とは異なるレコードに関連付けられてしまいます。そのため、データ移行や統合の際に、参照関係を維持するためにサロゲートキーを使用します。つまり、参照関係が変化しないように、参照先のレコードのサロゲートキーとマッピングすることで、データの関連性を保つのです。
具体的な例を示します。
例えば、以下の2つのテーブルがあるとします。
テーブル: Users
| user_id | user_name |
|---|---|
| 1 | Nanase |
| 2 | Miri |
| 3 | Ruka |
| 4 | Ririka |
テーブル: Orders
| order_id | user_id | order_date |
|---|---|---|
| 1 | 2 | 2023-09-01 |
| 2 | 3 | 2023-09-05 |
| 3 | 1 | 2023-09-08 |
ここで、Ordersテーブルのuser_idはUsersテーブルのuser_idを外部キーとして参照しています。これにより、Ordersテーブルの各注文が特定のユーザーに関連付けられています。
マッピング上でのサロゲートキー対応を考慮せずにデータを移行する場合、UsersテーブルとOrdersテーブルを別のデータベースに移行したとします。
移行元のデータベースのUsersテーブルからデータを移行する際、ユーザーの統合や再識別、再作成が行われたとします。すると、user_idが以下のように採番によって変わってしまうことが起こり得ます。
テーブル: Users
| user_id | user_name |
|---|---|
| 1 | Hazuki（統合により再作成された） |
| 2 | Nanase |
| 3 | Miri |
| 4 | Ruka |
| 5 | Ririka |
このような場合、OrdersテーブルのレコードとUsersテーブルの対応関係が崩れてしまい、注文データが正しく紐づけられなくなります。
対応例
以下は、具体的な手順の例です。
1.マッピングテーブルの作成
サロゲートキーと元のキー（ユーザーID）の対応関係を持つマッピングテーブルを作成します。このテーブルには、新しいサロゲートキーと元のキーの対応関係を保持するための列が含まれます。
マッピングテーブル: UserMapping
| surrogate_key | original_key(user_id) |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
2.マッピングテーブルの更新
マッピングテーブルを使用して、Ordersテーブルのuser_id列をサロゲートキーに対応させます。具体的には、Ordersテーブルのuser_id列の値とマッピングテーブルを参照して、対応するサロゲートキーを取得します。
Ordersテーブル: Orders
| id | user_id | order_date |
|---|---|---|
| 1 | 2 | 2023-09-01 |
| 2 | 3 | 2023-09-05 |
| 3 | 1 | 2023-09-08 |
更新後のOrdersテーブル: Orders
| id | user_id | order_date |
|---|---|---|
| 1 | 1 | 2023-09-01 |
| 2 | 2 | 2023-09-05 |
| 3 | 3 | 2023-09-08 |
3.テーブルの移行
移行後のOrdersテーブルでは、user_id列がサロゲートキーに対応しています。これにより、参照関係が正しく保たれます。
まとめ
移行先のデータベースで参照先のレコードの順序やIDが異なる場合、サロゲートキーを利用してデータの関連性を保つことができます。
参考
Discussion