できるエンジニアになるためのちょい上DB術/第2章 概念設計
3.3 論理設計の手順(4/4)
3.3.5 一意識別子の見直し
一意識別子に人工キーを使用する
受注明細表の一意識別子は、概念設計が完了した時点で以下の5つの属性の組み合わせになると考えられます。
- 受注番号(FK)
- 商品コード
- ロット番号
- 受注明細種類コード
- 受注明細種類コード連番
受注明細表は次の特徴をもちます。
- 他の「複数の」エンティティから参照されるエンティティであること
その場合、他のエンティティからの参照キーは受注明細表の一意識別子であり、他のエンティティの外部キー列として挿入されることになり、容量的にも多くの領域をとることになります - 他のエンティティと結合して参照される、または更新のためにアクセスされる頻度が高い表であること
5つの属性の組み合わせで一意識別子とすると、他のエンティティとの結合の場合、5つの属性値を比較して結合条件を満たすことを確認する必要があり、結合のコストが高くなってしまいます
このような場合、論理設計の段階で人工キーを作成します。
受注明細の場合、受注エンティティとの結合キーである「受注番号」と、受注番号ごとに一意の値を発番する人工キー「受注明細番号」を組み合わせて一意識別子とすることを考えます。
人工キーを作成する基準が明確にあるわけではないのですが、次のポイントを目安にしてください。
- 頻繁に他のエンティティと結合する
- 複数のエンティティと結合する
- 複数の属性の組み合わせで一意識別子が定義される
人工キーは安易につけられることが多いのですが、次の点に気をつけて、本来の一意識別子の意味をはずさないようにしてください。
人工キーには意味がないので、何の定義をもって一意なオカレンスとするかの意味が失われてしまいます。
そのため、人工キーにつける主キー制約の本来の意味はなく、誤って重複してはいけない属性値の組み合わせでオカレンスが作成されてしまう可能性があります。
そこで、整合性を維持するために、元の一意識別子の組み合わせでユニーク制約を設定する場合もあります。
次に、受注明細の人工キーについて、クイズで考えてみてください。
クイズ3
受注明細テーブルの主キーはこのままでよいか検討しなさい。
- 【受注番号+商品コード】
同じ商品を追加で受注した場合、別の行としては挿入できない - 【受注番号+行番号】
行の途中挿入をしたり削除をすると表示が意図どおりにならない
(同じ商品は続きの行に表示したい。しかしそこに挿入するとその後の行番号(主キー列の値)をすべてずらさなければならなくなる)
クイズ3の解答例
- 1.「行連番」という連番キーを内部的に追加する
- 2.「売上番号+行連番」=主キーとする
- 3.「表示行番号」という属性も追加し、非主キー列として画面や帳票における表示番号を表す数値列として追加する。
既存の売上行に追加で行を挿入する場合は、非主キー列の順序は再採番する
属性、一意識別子の見直しが完了したら、それぞれの値の整合性を維持するための一貫性制約について見ていきます
3.3.6 一貫性制約の検討
一貫性制約とは
データベースの信頼性を維持するために、DBMSの機能として実装されているものと、アプリケーションで実装するものがあります。
DBMSの制約は、アプリケーションで実装するわけではないので、漏れや抜けがなく、データベースに実際の値が入る直前にチェックし、これに違反するデータは絶対に入れないというルールを確立することができます。
- データの整合性を保証するためのルール
- データは、定義された制約に基づき、挿入、更新、削除される
- 制約は、RDBMSの機能として実装されているものもあるが、開発者がDBトリガーなどを使って実装しなければならないものもある
一貫性制約の種類
以下の種類に分類されます。それぞれの例を順に説明します。
- 参照制約
- 削除制約
- ビジネスルール
参照制約:リレーションに関する制約
エンティティ間にリレーションが存在する場合、多側のオカレンスを定義する属性に、そのオカレンスが1側のいずれのオカレンスに対応するべきかを決めるための「外部キー列」が必要です。
そして、外部キーとして扱われている属性は、他のテーブルの「キー」として定義されている必要があります(1側の相手のオカレンスを一意に決めるものである必要があります)。
この例では、受注明細エンティティにある商品番号は、商品エンティティのキーとして定義されている必要があるということになります。
Oracleデータベースでは、多側の外部キーに参照制約を宣言するためには、その前に、外部キーの属性が、1側のエンティティで主キーまたはユニーク制約を宣言されている必要があるという制約があります。
削除制約:参照制約の中で削除に関する制約
エンティティオカレンスが削除された、もしくは削除要求がある場合、関係のあるエンティティオカレンスや自分自身をどのように扱うべきかのルールです。削除制約には以下の種類があります。
削除制約の種類 | 内容 |
---|---|
CASCADE | 子側(多側)が親に依存しているので、親のオカレンスを削除した場合、子側のオカレンスも同時に削除される 第1正規化を行ったエンティティ間に多い Oracleデータベースでは、参照制約のオプションとして設定することができる |
SET NULL | 親を削除した場合、子側のオカレンスは残るが、外部キーの値をNULLにする 子供が宙ぶらりん状態になるような状態 第2正規化から第3正規化を行ったエンティティに多い SET NULL制約を設定するのであれば、あらかじめ子側に、親の必要な属性を重複してもたせておく場合が多い Oracleデータベースでは、参照制約のオプションとして設定できる |
RESTRICTION | 参照関係がある子側のオカレンスが存在する場合、参照されている親オカレンスは削除できない Oracleデータベースの参照制約のデフォルト |
表3-12 削除制約
ビジネスルール:エンティティの属性に関する制約
Oracleデータベースでは.エンティティの属性のビジネスルールとして、表を作成する際に定義できる制約があります。
Oracleデータベースの制約は、制約が有効であれば、値が入る前にチェックされ、それに違反するオカレンスはデータベース内に挿入されることはないという厳しい制限がかけられます。
not null制約は、オカレンスが生成される時点から値が設定されている必要があることを定義しています。
check optionは、列の値が設定した式の範囲を超えないという制約を満たす必要があります。
unique制約は、列の値が必ず一意である必要があります。
この制約を高速にチェックするため、unique制約が列、または列の組み合わせに定義されると、その列または列の組み合わせに索引が定義されていなければ、索引が作成されます。
ただし、値がnullであることを許すという制約です。
一意性のチェックをより高速に行いたいのであれば、事前に該当の列または列の組み合わせにunique indexを作成しておくことをお勧めします。
primary key制約は、列の値が必ず一意であり、null値も許さないという制約です。
また、この制約は表に対して1つしか設定できません。
この制約を高速にチェックするため、Primary制約が列、または列の組み合わせに定義されると、その列または列の組み合わせに索引が定義されていなければ、索引が作成されます。
この制約の一意性チェックをより高速に行いたいのであれば、事前に該当の列または列の組み合わせにunique indexを作成しておくことをお勧めします。
解説トレーナー