正確性と制約

はじめに

データを保存することと、意味があり、有用で、正しいデータを保存することは全く別の話です。意味と有用性はそれ自体主観的な特性ですが、正確性は少なくとも論理的に定義し、強制することができます。型はすでに数値が数値であり、日付が日付であることを保証しますが、重さや距離が正の数値であることや、日付範囲が重複することを防ぐことはできません。タプル、テーブル、データベースの制約は、保存されるデータにルールを適用し、要件を満たさない値や値の組み合わせを拒否します。

制約は、同じアサーションをテストする場合でも、他の入力検証技術を無意味にするものでは決してありません。無効なデータを保存しようとして失敗する時間は無駄です。システムやアプリケーションのプログラミング言語におけるassertのような違反メッセージは、データベースに直接関与していない人が必要とするよりもはるかに詳細に、最初の候補レコードの最初の問題のみを明らかにします。しかし、データの正確性に関する限り、制約は良くも悪くも法であり、それ以外は助言に過ぎません。

タプルについて: NOT NULL、DEFAULT、CHECK

NOT NULL制約は最も単純なカテゴリです。タプルは制約された属性に値を持たなければならず、言い換えれば、その列に許可される値のセットに空のセットは含まれません。値がないということはタプルがないということであり、挿入または更新は拒否されます。

NULL値からの保護は、CREATE TABLEまたはADD COLUMNでcolumn_name COLUMN_TYPE NOT NULLを宣言するのと同じくらい簡単です。NULL値はデータベースとエンドユーザーの間で多くの問題を引き起こすため、NULLを許可する正当な理由がない限り、どの列にも反射的にNOT NULL制約を定義することは良い習慣です。

挿入または更新において何も指定されていない場合（省略または明示的なNULLによる）、デフォルト値が提供されることは、候補レコードが拒否されずに変更されて保存されるため、常に制約とは見なされません。多くのDBMSでは、デフォルト値は関数によって生成される場合がありますが、MySQLはこの目的のためにユーザー定義関数を許可していません。

単一のタプル内の値のみに依存する他の検証ルールは、CHECK制約として実装できます。ある意味で、NOT NULL自体はCHECK (column_name IS NOT NULL)の短縮形であり、違反時に受け取るエラーメッセージがほとんどの違いを生み出します。しかし、CHECKは、単一のタプルに対する任意のブール述語の真偽を適用し、強制することができます。例えば、地理的位置を保存するテーブルはCHECK (latitude >= -90 AND latitude < 90)とするべきであり、経度についても-180から180の間で同様に、利用可能であればGEOGRAPHYデータ型を使用し、検証すべきです。

テーブルについて: UNIQUE、EXCLUSION

テーブルレベルの制約は、タプルを相互にテストします。UNIQUE制約では、制約された列に対して、与えられた値のセットを持つレコードは1つだけです。NULL可能性はここで問題を引き起こす可能性があります。NULLはNULL自体を含め、他の何とも等しくないためです。(batman, robin)に対するUNIQUE制約は、したがって、ロビンがいないバットマンの無限のコピーを許可します。

排他制約（Exclusion constraints）はPostgreSQLとDB2でのみサポートされていますが、非常に有用な役割を果たします。重複を防ぐことができるのです。制約対象のフィールドと、それぞれが評価される操作を指定すると、新しいレコードは、既存のレコードが各フィールドと操作で正常に比較されなかった場合にのみ受け入れられます。例えば、schedulesテーブルは競合を拒否するように設定できます。

-- text, int, etc. comparisons in exclusion constraints require this
-- Postgres extension
CREATE EXTENSION btree_gist;

CREATE TABLE schedules (
  schedule_id SERIAL NOT NULL PRIMARY KEY,
  room_number TEXT NOT NULL,
  -- a range of TIMESTAMP WITH TIME ZONE provides both start and end
  duration TSTZRANGE,
  -- table-level constraints imply an index, since otherwise they'd
  -- have to search the entire table to validate a candidate record;
  -- GiST (generalized search tree) indexes are usually used in
  -- Postgres
  EXCLUDE USING GIST (
    room_number WITH =,
    duration WITH &&
  )
);

INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- the same time in a different room: accepted
INSERT INTO schedules (room_number, duration)
VALUES ('32B', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- a half-hour overlap for an already-scheduled room: rejected
INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:30:00Z,2020-08-20T11:30:00Z)');

Upsert操作、例えばPostgreSQLのON CONFLICT句やMySQLのON DUPLICATE KEY UPDATEは、テーブルレベルの制約を使用して競合を検出します。そして、NOT NULL制約がCHECK制約として表現できるのと同様に、UNIQUE制約は等価性に対する排他制約として表現できます。

主キー

UNIQUE制約には特に有用な特殊なケースがあります。UNIQUEな列または複数の列にNOT NULL制約を追加すると、テーブル内の各レコードは、制約された列の値によって一意に識別でき、これらは総称してキーと呼ばれます。usersテーブルが異なるUNIQUEかつNOT NULLなemailとusernameを持つ場合があるように、複数の候補キーがテーブル内に共存できます。しかし、主キーを宣言することで、レコードが公に、そして排他的に認識される単一の基準が確立されます。一部のRDBMSは、この目的のためにクラスター化インデックスと呼ばれる主キーによって、ページ上の行を整理することさえあり、主キー値による検索を可能な限り高速にします。

主キーには2つの種類があります。ナチュラルキーはテーブルのデータに「自然に」含まれる列または複数の列に定義される一方、サロゲートキーまたは合成キーはキーとなるためだけに考案されます。ナチュラルキーは注意が必要です。データベース設計者がしばしば考える以上に、名前からナンバリングスキームまで、多くのことが変更される可能性があります。国や地域名を含むルックアップテーブルは、それぞれのISO 3166コードを安全なナチュラル主キーとして使用できますが、名前やメールアドレスのような変更可能な値に基づくナチュラルキーを持つusersテーブルは問題を引き起こす可能性があります。迷った場合は、サロゲートキーを作成してください。

ナチュラルキーが複数の列にまたがる場合、複数列キーは管理に手間がかかるため、少なくともサロゲートキーを常に検討すべきです。しかし、ナチュラルキーが適切であれば、列はインデックスと同様に特異性（specificity）の昇順で並べるべきです。つまり、国コードの次に地域コードであり、逆ではありません。

サロゲートキーは、歴史的に単一の整数列、または最終的に数十億が割り当てられる可能性がある場合にはBIGINTでした。リレーショナルデータベースは、シリーズの次の整数でサロゲートキーを自動的に埋めることができます。この機能は通常、SERIALまたはIDENTITYと呼ばれます。

自動インクリメントする数値カウンターには欠点がないわけではありません。事前に生成されたキーを持つレコードを追加すると競合が発生する可能性があり、シーケンシャルな値がユーザーに公開されると、他の有効なキーが何であるかを推測されやすくなります。UUID（Universally Unique Identifiers）はこれらの弱点を回避し、サロゲートキーの一般的な選択肢となっていますが、単純な数値よりもページ内でのサイズがかなり大きくなります。v1（MACアドレスベース）とv4（擬似乱数）のUUIDタイプが最も頻繁に使用されます。

データベースについて: 外部キー

リレーショナルデータベースは、多テーブル制約の1つのクラス、つまり"部分集合要件"または外部キーのみを実装しています。この唯一の制約タイプが、テーブル間の不整合を防ぎ、リレーショナルデータベースをスプレッドシートと区別する原則である参照整合性の保証人です。

この非公式な「エンティティ関係図」またはERDは、図書館とその蔵書、利用者のデータベースのスキーマの始まりを示しています。各エッジは、接続するテーブル間の関係を表します。「|」記号はその側の単一レコードを示し、「カラスの足」記号は複数を示します。つまり、1つの図書館には多くの本があり、多くの利用者がいます。

外部キーは、別のテーブルの主キーのコピーであり、列ごとに（サロゲートキーに有利な点：コピーして参照する列が1つだけです）、このテーブルのレコードをその「親」レコードにリンクする値を持っています。上記のスキーマでは、booksテーブルは本を所蔵するlibrariesへのlibrary_id外部キーと、著者であるauthorsへのauthor_id外部キーを保持しています。しかし、authorsに存在しないauthor_idを持つ本が挿入された場合、どうなるでしょうか？

外部キーが制約されていない場合、つまり単なる別の列または複数の列である場合、本に存在しない著者がいる可能性があります。これは問題です。誰かがbooksとauthorsの間のリンクをたどろうとしても、行き止まりになります。authors.author_idが連番整数である場合、偽のauthor_idが最終的に割り当てられるまで誰も気づかず、最初は不明な誰かに、次にピエール・メナールに帰属された特定の『ドン・キホーテ』のコピーができてしまい、ミゲル・セルバンテスはどこにも見当たらない、という可能性もあります。

外部キーを制約しても、誤ったauthor_idがauthors内の既存のレコードを指している場合に本が誤って帰属されることを防ぐことはできません。そのため、他のチェックやテストも引き続き重要です。しかし、既存の外部キー値のセットは、ほとんどの場合、可能な外部キー値のほんの一部に過ぎないため、外部キー制約はほとんどの誤った値を捕捉し、防ぐことができます。外部キー制約があれば、存在しない著者の『ドン・キホーテ』は記録されずに拒否されます。

特定の正しい値のために

ここで述べられているよりも、データが不正である可能性ははるかに多くあります。制約は役立ちますが、それらも柔軟性に限りがあります。列内の値が出現できる回数を2回以上に制限するなど、多くの一般的なテーブル内仕様は、トリガーによってのみ強制できます。

しかし、テーブルの構造自体が不整合を招く可能性もあります。これを防ぐためには、主キーと外部キーの両方を定義して検証するだけでなく、テーブル間の関係を正規化するために組織する必要があります。ただし、その前に、テーブル間の関係がデータベース自体の構造をどのように定義するかについては、まだ表面をなぞったに過ぎません。

FAQ