制約を使用してデータ整合性を適用する
制約とシーケンス オブジェクトは、発生する前にデータの問題を防ぐ設計上の選択肢です。 外部キー制約が存在しない場合は、孤立したレコードがデータベースに既に存在している可能性があることを意味します。 後でシーケンス オブジェクトを追加して ID 列を置き換えるには、すべてのアプリケーションで変更が必要です。 アプリケーション コードではデータを検証できますが、ユーザーは一括インポート、直接クエリ、または検証をスキップする新しいアプリケーションを使用してデータをバイパスできます。
データベース制約では、エンジン レベルで規則が適用されるため、常に適用されます。 設計時に行う決定 (データベースで適用する規則、ID 列またはシーケンスを使用するかどうかなど) は、アプリケーションの有効期間のデータ品質に影響します。
制約を使用するタイミングを理解する
データ品質の問題はコストがかかります。 データ品質が低いと、不適切なビジネス上の決定、統合の失敗、コンプライアンス違反につながります。 同じデータベースにアクセスする異なるアプリケーション間で矛盾する可能性があるアプリケーション レベルの検証とは異なり、制約では、アプリケーション コード、アドホック クエリ、直接 SQL スクリプト、または一括インポートではバイパスできない、データベース エンジン レベルで規則が適用されます。 すべての INSERT、 UPDATE、および DELETE 操作は、データベース エンジンが変更をコミットする前に、定義されているすべての制約を満たす必要があります。
データベース制約の適用
制約により、データベースが破損する前にデータ品質の問題が防止されます。 次の表は、各制約の種類が特定のデータ整合性の問題にどのように対処するかを示しています。
| 問題 | 制約 | 例 |
|---|---|---|
| 孤立したレコード | 外部キー | 有効な顧客がいない注文を禁止します |
| データの重複 | UNIQUE | 重複する電子メールの登録を停止します |
| 無効なデータ | CHECK | 負の価格または将来の生年月日を拒否します |
| 重要なデータが見つからない | NOT NULL | 不完全なレコードを防ぎます |
| 参照の不整合 | 外部キー | テーブル間でデータの整合性を維持する |
顧客の電子メール列に一意の制約を定義していない小売企業について考えてみましょう。 時間の経過と共に、同じ顧客が同じメール アドレスで複数回登録されました。 マーケティングがプロモーション キャンペーンを送信したとき、一部の顧客は同じメールのコピーを 3 つ受け取り、コストを増やし、顧客の信頼を損ないました。 テーブル定義に UNIQUE (EmailAddress) を追加すると、これらの重複が挿入されなくなります。
制約により、データベース エンジン レベルで規則が適用され、データがシステムに入る方法に関係なくデータ品質が確保されます。 アプリケーションの検証はバイパスでき、アプリケーションによって異なり、保守が困難です。 データベース制約は常に適用され、一元化され、信頼の 1 つのソースを提供します。
制約により、データベース レベルでデータの品質と一貫性が確保されます。
主キー制約を使用する
主キー制約 は、一意のデータを保証し、エンティティの整合性を強制します。 主キー制約を指定すると、データベース エンジンによって主キー列の一意のインデックスが自動的に作成されます。 テーブルに含めることができる主キー制約は 1 つだけであり、主キー制約内で定義されているすべての列を NOT NULLとして定義する必要があります。
PRIMARY KEY制約を使用して主キーを作成できます。 次に例を示します。
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY IDENTITY(1,1),
EmailAddress NVARCHAR(100) NOT NULL
);
外部キー制約を使用する
外部キー制約では、外部キー テーブルに格納できるデータを制御することで参照整合性が適用されます。 外部キー制約を使用すると、主キー テーブル内のデータへの変更が外部キー テーブル内のデータへのリンクを無効にした場合に防止されます。
、CASCADE、SET NULLなどのSET DEFAULTを定義して、既存の外部キーがポイントするキーをユーザーが削除または更新しようとしたときの動作を指定できます。
外部キー列にインデックスを手動で作成する必要はありませんが、外部キー列は結合条件で頻繁に使用されるため、多くの場合便利です。
外部キーは、FOREIGN KEY句でREFERENCES制約を使用して作成できます。 次に例を示します。
CREATE TABLE Order (
OrderID INT PRIMARY KEY IDENTITY,
CustomerID INT NOT NULL,
OrderDate DATETIME2,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
一意の制約を使用する
一意の制約 により、主キーに含まれない特定の列に重複する値が入力されないようにします。
PRIMARY KEY 制約とは異なり、UNIQUE 制約は NULL 値を許容できます。 ただし、UNIQUE 制約が適用される他の値と同様に、NULL 値も 1 列に 1 つしか使用できません。 データベース エンジンは、一意性の要件を適用するために、一意の非クラスター化インデックスを自動的に作成します。
UNIQUE キーワードを使用して、一意の制約を作成できます。 次に例を示します。
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
SKU NVARCHAR(50) UNIQUE,
ProductName NVARCHAR(100)
);
check制限を使用する
Check 制約 では、1 つ以上の列で受け入れられる値を制限することで、ドメインの整合性が強制されます。 論理演算子に基づいてCHECKまたはTRUEを返す任意の論理式を使用して、FALSE制約を作成できます。 複数の CHECK 制約を 1 つの列に適用することも、1 つの CHECK 制約を複数の列に適用することもできます。
null 値は UNKNOWNに評価されるため、式内に存在すると制約がオーバーライドされる可能性があります。 たとえば、MyColumn = 10列の制約INTでも、NULLがNULLに評価されないため、FALSEを挿入できます。
論理式で CHECK キーワードを使用して CHECK 制約を作成できます。 次に例を示します。
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
HireDate DATE,
Salary DECIMAL(10,2),
CHECK (Salary >= 20000),
CHECK (HireDate <= GETDATE())
);
既定の制約を使用する
既定の制約 は、 INSERT 操作中に値が指定されていない場合に既定値を提供します。 データベース プロジェクトを使用する場合は、環境によって異なるシステム生成名を許可するのではなく、明示的な名前で制約を作成することをお勧めします。
default 制約は、 DEFAULT キーワードを使用して作成できます。 次に例を示します。
CREATE TABLE Activity (
ActivityID INT PRIMARY KEY IDENTITY,
Description NVARCHAR(200),
CreatedDate DATETIME2 CONSTRAINT DF_Activity_CreatedDate DEFAULT GETUTCDATE(),
IsActive BIT CONSTRAINT DF_Activity_IsActive DEFAULT 1
);
シーケンス オブジェクトを使用する
シーケンス オブジェクトは、シーケンスが作成された仕様に従って数値のシーケンスを生成する、ユーザー定義のスキーマ バインド オブジェクトです。 ID 列とは異なり、シーケンスは特定のテーブルに関連付けられません。 アプリケーションはシーケンス オブジェクトを参照して次の値を取得し、シーケンスとテーブル間のリレーションシップはアプリケーションによって制御されます。
ID 列は、1 つのテーブルに自動番号付けが必要な場合に適しています。 ただし、1 つのテーブルに制限されます。 複数のテーブル間で数値を共有したり、行を挿入する前に次の値を取得したり、カウンターを簡単にリセットしたりすることはできません。 シーケンス オブジェクトは、どのテーブルにも関係なく数値を生成することで、これらの問題を解決します。
シーケンスを使用するタイミングを理解する
シーケンスは、次のシナリオで ID 列の代わりに使用します。
- 共有番号系列 - アプリケーションでは、複数のテーブル間またはテーブル内の複数の列間で 1 つの一連の数値を共有する必要があります。
- 循環番号系列 - 指定した数に達すると、アプリケーションは番号系列を再起動する必要があります。 たとえば、1 ~ 10 の値を割り当てた後、アプリケーションは再び 1 ~ 10 の値を割り当てます。
-
並べ替えられたシーケンス値 - アプリケーションでは、シーケンス値を別のフィールドで並べ替える必要があります。
NEXT VALUE FOR関数は、OVER句を適用できます。これにより、返される値がORDER BY句の順序で生成されます。 -
複数の数値を予約 する - アプリケーションでは、一度に複数の連続する番号を予約する必要があります。 ID 値を要求すると、他のプロセスが同時に番号を発行した場合、ギャップが発生する可能性があります。
sp_sequence_get_rangeを呼び出すと、シーケンス内のいくつかの数値が一度に取得されます。 - 変更可能な仕様 - 作成後に、インクリメント値などのシーケンスの仕様を変更する必要があります。
シーケンス オブジェクトは、ID 列よりも柔軟性が高い場合があります。
| 特徴 | Sequence | アイデンティティ |
|---|---|---|
| テーブルに結び付けられた | いいえ | イエス |
| テーブルまたは列間で共有される | イエス | いいえ |
| 挿入操作の前に次の値を取得する | イエス | いいえ |
| カスタム最小値/最大値 | イエス | 制限あり |
| 一度に複数の数値を取得する | イエス | いいえ |
| 指定した回数でサイクル/再起動 | イエス | いいえ |
| 別のフィールドで値を並べ替える | イエス | いいえ |
| 作成後のインクリメントの変更 | イエス | いいえ |
1 つのテーブルに対して単純な自動作成主キーが必要で、複数のテーブルで同じ数値系列を共有したり、行を挿入する前に次の値を取得したりする必要がない場合は、ID 列を使用します。
挿入前にアプリケーションで数値が必要な場合、複数のテーブル間で 1 つの系列を共有する必要がある場合、指定した数に達したときに番号系列を再起動する必要がある場合、または一度に複数の連続番号を予約する必要がある場合は、シーケンスを使用します。
シーケンスの制限点について
ID 列とは異なり、シーケンス値はテーブルへの挿入後 に自動的に保護されません 。 また、シーケンス値に一意性は自動的に適用されません。 テーブル内のシーケンス値が一意である必要がある場合は、列に一意の制約を作成します。
シーケンス番号は、現在のトランザクションの範囲外で生成されます。 シーケンス番号を使用するトランザクションがコミットされるかロールバックされるかにかかわらず、消費されるようになります。
シーケンス オブジェクトは、start、increment、range の省略可能なパラメーターを持つ CREATE SEQUENCE ステートメントを使用して作成できます。 次に例を示します。
-- Create sequence
CREATE SEQUENCE OrderNumber
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 999999
NO CYCLE;
-- Use sequence in INSERT with NEXT VALUE FOR function
INSERT INTO Order (OrderID, CustomerID, OrderNumber, OrderDate)
VALUES (1, 100, NEXT VALUE FOR OrderNumber, GETDATE());
-- Get next value before INSERT
DECLARE @NextOrderNum INT = NEXT VALUE FOR OrderNumber;
SELECT @NextOrderNum;
-- Get multiple sequence numbers at once for batch processing
DECLARE @FirstSeq INT, @LastSeq INT;
EXEC sp_sequence_get_range
@sequence_name = N'OrderNumber',
@range_size = 100,
@range_first_value = @FirstSeq OUTPUT,
@range_last_value = @LastSeq OUTPUT;
-- Reset sequence
ALTER SEQUENCE OrderNumber RESTART WITH 1000;
次の使用例は、 OrderNumber という名前のシーケンスを作成します。このシーケンスは 1000 から始まり、1 ずつインクリメントされ、循環せずに999999で停止します。
NEXT VALUE FOR関数は、アプリケーションが値を最初に参照する必要がある場合、INSERT ステートメント中でインラインで、または挿入の前に変数に割り当てることで、次に使用可能な数値を取得します。 一度に複数の連続する数値を必要とするバッチ操作の場合、 sp_sequence_get_range は 100 個の数値のブロックを予約し、範囲内の最初と最後の値を返します。
ALTER SEQUENCE ステートメントは、必要に応じ、シーケンスを 1000 にリセットします。
制約は、問題が発生する前に妨げるアーキテクチャ上の決定です。
CHECK制約がない場合、無効なデータによってデータベースが自動的に破損する可能性があります。 テーブル間の番号付けが必要な場合に ID 列を選択すると、アプリケーション レベルの回避策が強制されます。 データベース レベルで定義された制約により、データベースにアクセスするアプリケーション、ツール、またはスクリプトに関係なく、データ品質が保護されます。 これらの決定により、アプリケーションの有効期間に対するデータ整合性の保証が形成されます。