MySQL
SQL
PostgreSQL
DB
データベース

【プログラマのための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の度にトリガーが実行される。
  • 制約に違反する場合、トリガーが自動的にデータを調整してくれるわけではない。

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

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

が載っている。