この記事では、SQLにおけるUnique Constraintsの概要と、SQL ServerのUniqueインデックスについて説明します。
はじめに
SQL Server の制約では、SQL テーブルの列レベルでルールを定義することができます。 制約の追加には、Create table または Alter table ステートメントを使用します。
私たちは、ACIDのConsistencyプロパティに制約を使用しています。
ACIDプロパティについては、こちらの記事「SQL Server Transaction Overview」をご覧ください。
この記事では、SQL Server のユニーク インデックスとユニーク制約について説明します。
Overview of UNIQUE constraints in SQL Server
SQL Server の列で一意の値を確保することができます。 これは、単一の列に対しても、複数の列の組み合わせに対しても可能です。 これにより、ユニーク制約で結ばれた列に重複した値を持たないようにすることができます。 主キーの列も列の一意性を確保するものであることはご存知かと思います。
例えば、従業員テーブルがあり、その名の通り、すべての従業員の情報を保持しているとします。 その列には主キーがあります。 このテーブルは、従業員の社会保障番号も保持しています。 この社会保障番号の列には、重複した値を入れたくありません。
SSMS の GUI 方法で SQL テーブルを作成してみましょう。 データベースを展開し、「テーブル」->>「テーブル」を右クリックします。
カラムとそのデータタイプを指定し、「Nullsを許可」カラムのチェックを外します。
カラムを右クリックし、その上で「プライマリキーの設定」をクリックしてプライマリキーを有効にします。
以下のように、主キーの列にキーシンボルが置かれます。
ここで、列を右クリックして「インデックス/キー」を選択します。
以下のindexes/keysウィザードが開き、テーブルにすでに主キーがあるような既存のインデックスが表示されます。
[Add]をクリックすると、これを使って追加のインデックス/制約を定義することができます。
[General]グループで、SQL Server Indexを定義したい列を選択します。
この SQL Server インデックスのプロパティでは、IsUnique というプロパティの値を選択できます。
タイプでは、Unique key または Index から選択するオプションがあります。
Unique Keyを選択してみましょう。先ほどのオプション「Is Unique」がグレーアウトしているのがわかります。
SSMS は、GUI で行った作業のスクリプトを生成するオプションを提供します。 特に初心者がGUIとt-SQLの両方を学ぶには良いことだと思います。
Generate change script…をクリックすると、SQL Serverでテーブルを作成し、主キー制約を追加し、ユニーク制約を追加するためのt-SQLが表示されます。
このスクリプトをコピーして、保存せずにテーブルデザイナーのウィンドウを閉じます。 生成されたスクリプトを使用して、SQL Server にテーブルと一意の制約を作成します。 テーブル名は、今回のデモにふさわしい名前に変更しました。
|
div div div
1
2
3
4
5
6
7
8
9
10div
11
12
13
14
15
16
17
18
19
です。 20
21
22
23
24
25
26
27
28
です。 29
30
31
32
33
34
35
|
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Employee
(
EmployeeID int NOT NULL,
EmpName varchar(50) NOT NULL,
SocialSecurityNumber int NOT NULL
) ON
GO
ALTER TABLE dbo.Employee ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
EmployeeID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
GO
ALTER TABLE dbo.Employee ADD CONSTRAINT
IX_Unique_SSN UNIQUE NONCLUSTERED
(
SocialSecurityNumber
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
GO
ALTER TABLE dbo.Employee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
|
上記のスクリプトを実行します。 テーブル、主キー、ユニークキー制約が作成されます。 主キー制約とユニークキー制約のSQL Serverインデックスが作成されます。 sys.sp_helpindexシステムストアドプロシージャを使用して、テーブルの既存のインデックスを確認することができます。
|
1
2
|
となります。
EXEC sys.sp_helpindex @objname = N’Employee’
GO
|
このテーブルにいくつかの値を挿入してみて、列に重複を入力することが許されるかどうかを確認してみましょう。
以下のクエリでは、Ramは従業員のRajに対してすでに利用可能な値を列に入力しようとします。
|
1
2
3
|
Insert into Employee values(1,’Raj’,1111)
Insert into Employee values(2,’Shyam’,2222)
Insert into Employee values(3,’Ram’,1111)
|
最初の2行は正常に挿入されますが、3行目ではUnique key constraint violationに関するメッセージが表示されます。 また、出力には重複した値が表示されています。
Disabling Unique Constraints in SQL Server
以下の ALTER table ステートメントを使用して、一意の制約を無効にすることができます。
|
1
2
3
|
ALTER TABLE Employee
NOCHECK CONSTRAINT ALL
GO
|
このコマンドは正常に実行されました。
重複する値を入力しようとすると、やはり同じエラーメッセージが表示されます。
SQL Server の一意の制約は、一意の SQL Server インデックスも作成することがわかっています。
無効にしたい SQL Server インデックスを右クリックし、「無効」をクリックすると、以下のようになります。 インデックスを無効にする
別の方法として、ALTER INDEXコマンドを使用して、SQL Serverのインデックスを無効にすることができます。 このクエリでは、インデックス名とテーブル名を指定する必要があります。
|
1
2
|
ALTER INDEX IX_Unique_SSN ON Employee
DISABLE
|
列に重複した値を入力できるようになります。
テーブルの中に重複した値があります。 Unique Non-clustered Indexを有効にしてみましょう。 インデックスを有効にするには、インデックスを再構築する必要があります。
インデックスを再構築するには、インデックスを右クリックして、そのプロパティから「REBUILD」をクリックします。
以下のALTER INDEXコマンドを使用して再構築することもできます。
|
1
2
3
|
USE
GO
ALTER INDEX ON . REBUILD
|
重複したキーが存在し、ユニークキー制約では重複が許されないため、インデックスを有効にすることができません。 また、出力にも重複したキーが表示されます。
Drop unique constraints in SQL Server
ユニーク制約によって作成されたユニークなIndexをドロップすることはできません。 それを実行しようとすると、次のようなエラーメッセージが表示されます。 ユニーク制約が Index を使用しているため、明示的な drop index を許可しません。
|
1
2
|
DROP INDEX Employee.
GO
|
Alter Table Drop Constraintコマンドを使用して、インデックスをドロップすることができます。 ご存知のように、SQL Serverでは一意の制約を持つIndexを作成します。 このコマンドは、制約と一緒に Index をドロップします。
|
1
2
|
ALTER TABLE Employee
DROP CONSTRAINT IX_Unique_SSN;
|
以下のスクリーンショットで、現在Indexが存在しないことを確認できます。
これにより、ユニーク制約によって作成されたユニークなIndexを誰も誤って削除することができないという、さらなるメリットが得られます。
SQL Server でのユニーク インデックス
前回は、SSMS の GUI メソッドを使用して SQL Server でユニーク制約を作成しました。
テーブルを右クリックし、[デザイン]を選択してみましょう。
右クリックして「インデックス/キー」を選択し、「タイプ」欄から「インデックス」を選択します。
重複するキーを無視する、統計情報を再計算するなど、ユニーク インデックスのいくつかのオプションも有効にしています。
このindex/keysページを閉じて、スクリプトを生成してみましょう。
Okをクリックして、行った修正を保存します。
重複を削除すれば、Indexを作成してくれます。
SQL Serverにおけるユニークインデックスとユニークコンストレイントの違い
ユニークインデックスとユニークコンストレイントはどちらも似ていて、機能的な違いはありません。 クエリオプティマイザもユニークインデックスを使用して、コストを最適化した実行プランを作成します。 唯一の違いは、SQL Serverではユニーク制約によって作成されたユニークインデックスを直接ドロップすることができないことです。
ご存知のように、制約は、SQL Server テーブルに格納されたデータに対するビジネス ルールのようなものです。 Indexを直接扱わない場合は、一意の制約を作成する必要があります。 しかし、似たようなカラムに一意の制約とキーを定義してはいけません。
GUIでインデックスを見ても、ユニークキーとIndexを区別することはできません。
しかし、SQL Serverはその違いを知っています。 両方のインデックスをスクリプト化すると、両方のインデックスに異なるスクリプトが表示されます。 以下のように、最初のスクリプトは SQL Server のユニーク制約のために Add Constraint 句を持つ alter table を使用しているのに対し、後の部分は Create Non-Clustered Index ステートメントを使用しています。
それでも、SQL Server の一意制約よりも明示的に作成された一意インデックスの方が、いくつかの追加メリットがあります。
- クエリのパフォーマンスを向上させるために、非クラスタ化一意インデックスに列を含めることができます。 SQL Server は一意のインデックスのキー カラムに対してのみ一意性を強制します
- 一意のインデックスにフィルタを追加できます。 これは、NULL値を許可しているカラムにユニークインデックスを作成したい場合に役立ちます。 この場合、NULL以外の値に対して一意のインデックスが作成されるため、複数のNULL値を持つことができます
- また、一意のインデックスを参照する外部キーを定義することもできます
結論
この記事では、SQL Serverにおける一意の制約と一意のインデックスについて説明しました。 ユニーク制約を作成すると、ユニークインデックスも得られます。 クエリのパフォーマンスに違いはありませんので、どのようなオプションを選択しても構いません。
- Author
- Recent Posts
SQL Server Always On Availability Groupsに関する50のパートからなるシリーズでは、1つのトピックに関する最大の無料オンライン記事集の1つを作成しています。 SQL Serverコミュニティへの貢献に基づき、彼はSQLShackで2020年と2021年に連続して名誉ある「Best author of the year」を含む様々な賞を受賞しています。
ラージは常に新しいことに挑戦していますので、彼の著作で取り上げられているテーマについてコンサルティングが必要な場合は、
までご連絡ください。 [email protected]
View all posts by Rajendra Gupta
- Configure long-term backup retention for Azure SQL database – March 25, 2021年
- Azure SQL データベースのトランザクション的に一貫したコピーを作成する – 2021年3月22日
- Azure Cloud Shell の概要 – 2021年3月18日