正確性と制約

はじめに

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

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

タプルについて：not null、default、および check

非NULL制約は最も単純なカテゴリです。タプルは制約された属性の値を持っている必要があります。言い換えれば、列の許容値のセットには空集合は含まれなくなります。値がないということはタプルがないことを意味します。挿入または更新は拒否されます。

NULL値を防ぐことは、CREATE TABLEまたはADD COLUMNでcolumn_name COLUMN_TYPE NOT NULLを宣言するのと同じくらい簡単です。NULL値は、データベースとエンドユーザーの間でカテゴリ全体の多くの問題を引き起こすため、NULLを許可する正当な理由がない限り、任意の列に反射的に非NULL制約を定義することは、身につけるべき良い習慣です。

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

単一のタプル内の値のみに依存する他の検証ルールは、CHECK制約として実装できます。ある意味で、NOT NULL自体はCHECK (column_name IS NOT NULL)の省略形です。違反時に受信するエラーメッセージが、ほとんどの違いを生み出します。CHECKは、単一のタプルに対する任意のブール述語の真偽を適用および強制できます。たとえば、地理的位置を格納するテーブルは、CHECK (latitude >= -90 AND latitude < 90)、および同様に経度-180〜180（または、利用可能な場合はGEOGRAPHYデータ型を使用および検証）をCHECKする必要があります。

テーブルについて：uniqueとexclusion

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

除外制約は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など）は、テーブルレベルの制約を使用して競合を検出します。また、非NULL制約をCHECK制約として表現できるのと同様に、ユニーク制約は等価性の除外制約として表現できます。

主キー

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

主キーには2つのタイプがあります。自然キーは、テーブルのデータに「自然に」含まれる列または列で定義されますが、サロゲートキーまたは合成キーは、キーになる目的のためだけに作成されます。自然キーには注意が必要です。名前から番号付け方式まで、データベース設計者がしばしば考慮するよりも多くのことが変更される可能性があります。国と地域名を含むルックアップテーブルは、それぞれのISO 3166コードを安全な自然主キーとして使用できますが、名前やメールアドレスなどの変更可能な値に基づく自然キーを持つusersテーブルはトラブルを招きます。迷ったら、サロゲートキーを作成してください。

自然キーが複数の列にまたがる場合、複数列キーの管理にはより多くの労力がかかるため、サロゲートキーを常に少なくとも検討する必要があります。ただし、自然キーが適切である場合は、インデックスの場合と同様に、列を特異性を高める順序で並べる必要があります。つまり、国コードの後に地域コードを配置し、その逆ではないということです。

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

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

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

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

この非公式な「エンティティ関係図」またはERDは、図書館とそのコレクションおよび利用者のデータベースのスキーマの始まりを示しています。各エッジは、それが接続するテーブル間の関係を表します。| グリフは、その側の単一レコードを示し、「カラスの足」グリフは複数を示します。つまり、図書館は多くの本を保持し、多くの利用者を持っています。

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

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

外部キーを制約しても、誤ったauthor_idがauthorsの既存のレコードを指している場合に、本の誤った帰属を防ぐことはできません。したがって、他のチェックとテストが依然として重要です。ただし、既存の外部キー値のセットは、ほとんどの場合、可能な外部キー値の小さなサブセットであるため、外部キー制約はほとんどの間違った値をキャッチして防ぎます。外部キー制約を使用すると、存在しない著者を持つキホーテは記録されずに拒否されます。

ある程度の正確さについて

ここで取り上げたよりも、データが正しくない可能性のある方法は他にもたくさんあります。制約は役立ちますが、それでも柔軟性に限りがあります。列に値が表示される回数の制限（2回以上など）のような多くの一般的なテーブル内仕様は、トリガーでのみ強制できます。

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

FAQ