Posted at

【プログラマのためのSQL 3章】スキーマレベルのオブジェクト


はじめに

第3章は「スキーマオブジェクト」について。

データベースはただのテーブルの集まりではなく、他にも様々な概念がある。

どうしてもテーブルに目が行きがちだが、

各オブジェクトを包括する「スキーマ」や、

それらに付与できる「制約」といった概念は重要である。


SCHEMA(スキーマ)

「スキーマ」という言葉はとても抽象的でイメージしづらいが、

データベースをOS、テーブルをファイルと例えるなら、

ディレクトリに該当するものというイメージ。


  • テーブルなどの全てのオブジェクトは、必ず何かしらのスキーマに属する

  • スキーマ内には同名の同じオブジェクトを作成できない

  • スキーマには所有者、書き込み権限などの概念があり、それらのルールに従ってオブジェクトが作られる。

実際にはCREATE SCHEMA文でスキーマを作成する際は、

以下のようなことを定義する。


  • スキーマ名

  • ドメイン

  • ユーザのアクセス制限(grant)

  • スキーマ全体の制約(assertion)

  • ASCIIやUnicordなどのキャラクタセット(character set)

  • 文字列比較時の判定方法の指定(collection)

  • 異なるキャラクタセット間のマッピング設定(translation)

  • 物理ストレージの割り当など(各DB製品のオプション)


DOMAIN(ドメイン)

日本語で定義域と呼ぶ。

これも抽象的な言葉なので個人的に好きではない。

どうやら、データベースにおけるドメインは、

「ユーザが独自に定義するデータ型のこと」らしい。

ドメインは列に対するデータ型の宣言の代わりに使うことができる。

注意点として、ドメインを定義するときは基本的なデータ型を使って定義しないといけないため、ドメインの上にドメインを定義することはできない。


CHECK句

ドメインを始め、いくつかのオブジェクトに対して制約を儲けることができる。

例えば、米国の州コードを表すドメインを作成すると以下のようになる。

CREATE DOMAIN StateCode AS CHAR(2)

DEFAULT '??'
CONSTRAINT valid_state_code
CHECK (VALUE IN ('AL','AK','AZ',......));

もしドメインという概念がなかったら、

各テーブル定義にこのCHECK制約をコピーしなければならない。


SEQUENCE(シーケンス)

シーケンスは、呼び出されるたびに連続した値を生み出すジェネレータであり、関数のように使うことができる。

シーケンス機能は、現在では多くのDB製品で実装されているが、順序が付与されるということは集合指向ではなくなる非リレーショナルな拡張なので極力使うべきではない、とのこと。


ASSERTION(アサーション、表明)

「表明」とも呼ばれ、スキーマ内の複数のテーブルに対して制約を適用する。

この表明を使うことで、単一テーブルに付与するCHECK制約にはできないことが可能になる。

例えば、テーブルAの件数は、テーブルBとCの件数の合計に等しい、という論理的な制約を表現したい場合に表明が使える


CREATE ASSERTION assert_total_count
CHECK (SELECT COUNT(*) FROM TableA) =
(SELECT COUNT(*) FROM TableB) + (SELECT COUNT(*) FROM TableB);

テーブルに対するCHECK制約は「テーブルが空の時にTRUEになる」という挙動のため、表明を使わないとこの制約を実装するのは難しい。


スキーマレベルの制約の実装

ASSERTIONはとても便利そうに見えるが、

スキーマレベルの制約を実装する注意点としては以下の通りである。


  • ASSERTIONはまだ多くのDB製品で実装されていない。

  • MySQL等ではCHECK制約も実装されていない。

  • 代替としてはプロシージャやトリガーを使う必要がある。

  • トリガーはベンダー独自の手続きコードを書くことになるので注意。

  • テーブルの整合性を保つためにトリガーを使うと、INSERTやUPDATEの度にトリガーが実行される。

  • 制約に違反する場合、トリガーが自動的にデータを調整してくれるわけではない。

本書では、いくつか実装例が載っているのでぜひ確認して欲しい。


  • スキーマレベルの制約をトリガーを使って実現する方法

  • スキーマレベルの制約をテーブル分割(ビュー)とストアドプロシージャで実現する方法

  • 表明を使って実現する方法

が載っている。