ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

SQL Server : フィールドにIDENTITYを設定して自動的に連番が設定されるようにしたテーブルでIDENTITY_INSERTをONにして明示的に数値を指定したデータをINSERTする

with one comment

データを管理するとき、自動的に連番が設定されるようにしたい場合がある。これは例えばMicrosoft Accessでいう「オートナンバー型」のフィールドに相当する。Microsoft Accessの「オートナンバー型」は、データを生成するときに何も値を入れず、自動的に連番が設定されるフィールドだ。テーブルを構成するとき、このように自動的に連番が割り当てられるフィールドを作っておくと、その連番値自体に特段の意味はなくても、データを管理する場面で有効になる。システムの運用をはじめると、さまざまな想定外のことがおこる。間違ったデータを大量に作ってしまい特定の範囲のデータを全部まとめて修正しなければならない、重複したデータを作ってしまい後から作ったデータを削除しなければならない、ある一定期間に行われた処理に誤りがあり一括してデータを修正しなければならない、といったようなこことだ。これらの運用時に、データが生成された順番がわかっていると処理しなければならないデータの範囲を絞り込む重要な手掛かりになる。

Microsoft Accessには次のように「オートナンバー型」というデータ型がある。

SQL_identity_002_mid_640

<Fig.1 : Miceosoft Access 2013でフィールドのデータ型をオートナンバー型にする>

しかしSQL ServerにはAccessのようなオートナンバー型というデータ型はない。SQL ServerでAccessのオートナンバー型のように、数値が連番で自動的に設定されるようにするには、データ型を「bigint」、「decimal」、「int」、「numeric」、「smallint」、「tinyint」のような整数の性質を持っているものとし、列のプロパティで「IDENTITYの指定」の「(IDである)」を「はい」にし、「IDの増分」と「IDENTITYシード」を設定する。「IDENTITYシード」は最初に割り当てられる数値を意味し、「IDの増分」はデータが増えるたびに加えられる値を意味する。次の例は「IDENTITYシード」を10、「IDの増分」を3として作ったテーブルにデータをいくつか追加した例だ。

SQL_identity_004_mid_640

<fig.2 : IDフィールドのIDENTITYを指定し、IDENTITYシードを10、IDの増分を3と設定する>

SQL_identity_006_mid_640

<fig.3 : IDフィールドにIDENTITYを指定したテーブルにデータを3件追加した>

なおIDENTITYの設定は「float」、「real」のような実数型では指定することができない。また「money」、「smallmoney」でも指定することができない。

IDENTITYを指定したフィールドには明示的に値を指定することはできず、自動的に値が割り当てられるだけになる。例えば次のようなINSERT文は実行できない。

——————————————————————

insert into Table_1(ID,[values]) values(20,’testinsert’)

——————————————————————

SQL_identity_007_mid_640

<fig.4 : IDENTITYを指定したフィールドに明示的に値を指定したINSERT文を実行しエラーとなった>

IDENTITYを指定したフィールドは、たとえば次のように明示的に値を指定せずにINSERT文を実行することになるだろう。

——————————————————————

insert into Table_1([values]) values(‘testinsert’)

——————————————————————

SQL_identity_008_mid_640

<fig.5 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した>

SQL_identity_009_mid_640

<fig.6 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した結果>

INSERTされたデータのIDは、フィールドに指定した「IDの増分」だけ増えた値が自動的に割り当てられている。もちろんこれは期待どおりの結果だが、データの保守管理上はこのままでは困る場合が出てくる。たとえばあるテーブルのデータを別のテーブルに一時的に退避し、後に戻したいといった場合だ。データを退避するため「table_1」と同じフィールド構造をもつ「Table_2」を作っておくが、「Table_2」の「ID」フィールドのIDENTITYは設定しない。そして次のクエリのように、「Table_1」のデータを「Table_2」にコピーし、「Table_1」のデータを消去するが、そのあと、「Table_2」のデータを「Table_1」の戻すときにはエラーとなる。

——————————————————————

insert into Table_2(ID,[values]) select ID,[values] from Table_1
delete Table_1
insert into Table_1(ID,[values]) select ID,[values] from Table_2 –(エラーになる)

——————————————————————

SQL_identity_010_mid_640

<fig.7 : IDENTITYが設定されているフィールドに明示的に値を指定してINSERTしようとしてエラーになった>

このエラーを回避する最もわかりやすい方法は、「Table_1」の「ID」列のIDENTITY指定を一時的に解除することだ。しかし一時的ではあれテーブルのデザインを変更すると、そのあいだに他のユーザーが正しい方法でデータを挿入しようとしたときにエラーになるだろう。自分だけが使うデータベースであるとか、システムが小規模で他に与える影響がないと考えられる場合はいいかもしれないが、望ましいことではない。

このようにIDENTITYが設定されたフィールドに明示的に値を与えたいとき、IDENTITY_INSERTをONにする方法がある。先のエラーになったINSERT文の前後にIDENTITY_INSERT文を使おう。例えば次のクエリはエラーにならず成功する。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) select ID,[values] from Table_2
set identity_insert Table_1 off

——————————————————————

SQL_identity_011_mid_640

<fig.8 : IDENTITY_INSERTの設定によりINSERTが成功した>

set identity_insert Table_1 onによってIDENTITYを設定したフィールドにも明示的に値を与えることができるようになり、set identity_insert Table_1 offでデフォルトの状態に戻る。

たとえば次のようにIDに「20」という値を明示的に挿入するクエリも実行することができる。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) values(20,’ID20testdata’)
set identity_insert Table_1 off

——————————————————————

SQL_identity_012_mid_640

<fig.9 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした>

SQL_identity_013_mid_640

<fig.10 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした結果>

このように、テーブルにIDENTITYを設定したIDフィールドを持たせることはよくやることだが、IDENTITYを指定したフィールドには通常は明示的な値を与えることはないし、それはできないのだが、identity_insertをon、offすることによって明示的に値を与えることができ、これはデータの管理の手法として知っておくとよい。

コメント / トラックバック1件

Subscribe to comments with RSS.


コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

%d人のブロガーが「いいね」をつけました。