自動的に連続番号を振る： IDENTITY プロパティ

IDENTITY プロパティ

IDENTITYプロパティは、自動的に連番を振ることができる機能です。構文は、次のとおりです。

IDENTITY (初期値, 増分)

初期値には、採番を始める最初の値を記述します。増分には、採番する際に増やしていく数を記述 します。たとえば、IDENTITY (1,1) と記述した場合は、1から始まって1ずつ増加していく番 号（1、2、3、･･･）、IDENTITY (501,2) と記述した場合は、501から始まって2ずつ増加して いく番号（501、503、505、･･･）を作成できます。記述する際に、初期値と増分を省略した場合 には、IDENTITY(1,1) と解釈されます。

Let's Try

それでは、これを試してみましょう。

1. 「idTest」という名前のテーブルを作成し、「autoNum」列へ IDENTITY プロパティを設 定します。

USE sampleDB

CREATE TABLE idTest

( autoNum int IDENTITY (1,1) ,col1 char(10)

,col2 char(10) )

このように、IDENTITY プロパティは、テーブルの作成時に列に対して設定します。

データの挿入

1. 次に、「idTest」テーブルへデータを何件か追加してみましょう。IDENTITY プロパティを設 定したテーブルへデータを追加（INSERT）するには、IDENTITY プロパティを設定した列以 外（今回は、col1 と col2）を指定して値を記述します。

72 -- データの INSERT

INSERT INTO idTest (col1,col2) VALUES ('aaa', 'xxx') INSERT INTO idTest (col1,col2) VALUES ('bbb', 'yyy') INSERT INTO idTest (col1,col2) VALUES ('ccc', 'zzz')

-- データの確認 SELECT * FROM idTest

データの追加後、SELECT ステートメントで結果を確認すると、「autoNum」列には、「1、

2、3」と 1 からの連続番号が割り振られていることを確認できます。このように、IDENTITY プロパティを使用すると、自動的に連続番号を振ることができるので便利です。ただし、

IDENTITY プロパティを設定できる列は、テーブル内で 1つだけです。

Note： テーブル デザイナーで IDENTITY プロパティの設定

IDENTITY プロパティは、Management Studio の「テーブル デザイナー」機能を利用して、GUI 操作で設定 することもできます。テーブル デザイナーで IDENTITY プロパティを設定するには、次のようにオブジェクト エ クスプローラーで、設定したいテーブルを右クリックして、［デザイン］をクリックします。

テーブル デザイナーが表示されたら、IDENTETY プロパティを設定したい列を選択し、［列のプロパティ］タブ にある「IDENTITY の指定」で「はい」を選択して、「IDの増分」へ増分値、「IDENTITY シード」へ初期値を 設定します。

IDENTITYプロパティ を設定したい列を選択 2

「IDENTITYの指定」で

「(ID である)」を「はい」

「IDの増分」は「増分」

「IDENTITYシード」は「初期値」

3

「列のプロパティ」タブ

1

IDENTITY を設定した列へ明示的に値を追加： IDENTITY_INSERT

IDENTITY プロパティを設定した列（以下、IDENTITY 列）には、デフォルトでは明示的に値を

追加することができません。値を追加しようとすると、次のようにエラーが発生します。

値を追加したい場合には、次のように SET ステートメントを使って IDENTITY_INSERT を ON へ設定する必要があります。

SET IDENTITY_INSERT テーブル名 ON

Let's Try

それでは、これを試してみましょう。

1. まずは、SET IDENTITY_INSERT を ON へ設定します。

SET IDENTITY_INSERT idTest ON

2. 次に、「autoNum」列（IDENTITY 列）へ、値を指定して INSERT ステートメントを実行 してみます。

-- データの INSERT

INSERT INTO idTest (autoNum, col1, col2) VALUES (50, 'ddd', 'zzz')

-- データの確認 SELECT * FROM idTest

IDENTITY 列へ

「50」という値を追加 しようとしている

74

「50」を IDENTITY列へ追加できたことを確認できます。このように IDENTITY_INSERT を ON へ設定すると、IDENTITY列へデータを追加できるようになります。

明示的に追加した後の採番

1. 次に、SET IDENTITY_INSERT を OFF へ戻して、この後に自動採番される値がどうなる かを試してみましょう。

SET IDENTITY_INSERT idTest OFF

INSERT INTO idTest (col1, col2) VALUES ('eee', 'zzz') SELECT * FROM idTest

結果は、「51」が採番されます。このように、明示的に値を追加した後は、その値を基準に増 分値が割り当てられるようになります。

現在の IDENTITY 値を調べる： IDENT_CURRENT 関数

1. 現在の IDENTITY 値（現在の最大値）は、IDENT_CURRENT というシステム関数を利用 して、調べることができます。これは、次のように利用します。

SELECT IDENT_CURRENT ('idTest')

結果は「51」が表示されて、「idTest」テーブルの現在の IDENTITY 値（最大値）を確認で きます。したがって、この次に採番される値は「52」となります。

データを削除した場合の ID 値

次に、データを削除した場合に、IDENTITY値がどうなるのかを試してみましょう。

1. 次のように、「idTest」テーブルの IDENTITY 列が「50」と「51」のデータを削除します。

DELETE FROM idTest WHERE autoNum >= 50

2. 続いて、新しいデータを追加して、IDENTITY 値を確認します。

INSERT INTO idTest (col1,col2) VALUES ('fff', 'zzz') SELECT * FROM idTest

新しく INSERT したデータには、「52」が採番されています。このように、DELETE ステー トメントでデータを削除しても、IDENTITY 値には影響がありません。

76

IDENTITY 値を変更する： DBCC CHECKIDENT

現在の IDENTITY 値（現在の最大値）を変更したい場合には、DBCC CHECKIDENT コマンド を利用します。構文は、次のとおりです。

DBCC CHECKIDENT ('テーブル名', RESEED, 変更後の任意の IDENTITY 値)

RESEED と指定することで IDENTITY 値を変更することができます。

それでは、これを試してみましょう。

1. idTest テーブルの IDENTITY 値が「4」から始まるように、現在の IDENTITY 値を「3」

へ変更してみます。

DBCC CHECKIDENT ('idTest', RESEED, 3)

「DBCC の実行が完了しました」と結果に表示されれば、成功です。

2. データを INSERT して、IDENTITY 値が変更されたことを確認してみましょう。

INSERT INTO idTest (col1, col2) VALUES ('ggg', 'zzz') SELECT * FROM idTest

追加したデータの IDENTITY 値が「4」になっていることを確認できます（現在の値を「3」

へ変更したので、「4」が割り振られています）。

現在のIDENTITY値 を”3” に変更

Note： PRIMARY KEY 制約違反に注意

DBCC CHECKIDENT で IDENTITY を変更した場合は、重複値が PRIMARY KEY 制約違反になることに注意す る必要があります。たとえば、「autoNum」列の値が5、6、7･･･と増えていって "52" になったとき、"52" の データは既に存在しているので、ここで重複値が発生します。このとき、PRIMARY KEY 制約を設定していない場 合には、そのまま重複値を格納することができますが、PRIMARY KEY 制約を設定している場合には、制約違反で エラーが発生してしまいます。

なお、エラーが発生した後に、もう一度データの INSERT を実行した場合には "53" が採番されます（制約違反 エラーが発生しても内部的な採番は行われています）。

Note： アプリケーションから IDENTITY 値を取得： SCOPE_IDENTITY 関数

追加された IDENTITY 値をアプリケーションから取得する場合は、他のユーザーが追加した IDENTITY 値を間 違って取得しないように注意する必要があります。前述の IDENT_CURRENT はあくまでもテーブル全体として の最新値で、自分が追加した値だという保証がないからです。そこで、これを解決してくれる（自分が追加した値 を保証してくれる）システム関数として「SCOPE_IDENTITY」が用意されています。アプリケーションから

IDENTITY 値を取得するには、必ず SCOPE_IDENTITY 関数を利用するようにしてください。この関数につい

ては、本自習書シリーズの「開発者のための Transact-SQL 応用」編で詳しく説明していますので、こちらもぜ ひご覧いただければと思います。

Note： Oracle の「シーケンス」、Access の「オートナンバー」

IDENTITY プロパティは、Oracle での「シーケンス」（SEQUENCE：順序）、Access での「オートナンバー」に 相当する機能です。

SQL Server 2012 からは、Oracle と同じように利用できるシーケンスがサポートされるようになったので、次の

ように連番を作成することも可能です。

CREATE SEQUENCE でシーケンスを作成

NEXT VALUE FOR で シーケンスから次の値を取得

78