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

InfoPath & SQL Server !

Posts Tagged ‘オートナンバー型

2019年5月18日(土) – 第2回神戸SQL Serverラボ – 神戸SQL Server User Group

leave a comment »

2019年4月から毎月定期開催で「神戸SQL Serverラボ」と題したSQL Serverの勉強会を始めました。場所は神戸三ノ宮駅から徒歩約5分の便利な場所にある、神戸青少年会館をお借りし、毎回テーマを決めて、初心者がSQL Serverデータベースの運用ができることを目指します。また経験者の方にも、それぞれお持ちのノウハウを交流できる場になれば幸いです。

<追記>
第2回からネットワーク環境で SQL Server を利用できるようにします。LAN ポートで有線接続できるか、WiFi 機能を持った PC をお持ちいただければ SQL Server をインストールしなくとも管理ツール SSMS だけ設定すれば実習ができます。用意する SQL Server のバージョンは SQL Server 2014 と SQL Server 2017 です。

場所:神戸青少年会館(神戸市勤労会館の5階6階です)
神戸市中央区雲井通5丁目1番2号 34.694927, 135.197403
kobeshiseishounenkaikan_map bingmapで表示
神戸市青少年会館のアクセスページ
日時:毎月第3土曜日 18:00~19:30

第2回 2019年5月18日(土)18:00~19:30
「テーブル作成、データ型、主キー、テーブル作成とSQL文」
データベースの作成とテーブル作成について実習します。実習の中でデータを一意に管理するテーブルの主キーについてや、Accessの「オートナンバー型」のようにデータに自動的に連番を与える方法、テーブル作成を SQL 文で行う方法などを体験的に身に着けます。
場所:神戸青少年会館 サークル4号室(神戸市勤労会館の6階です)

申し込み方法:電子メールまたはconnpassで
電子メール:kobesqlserverlabo@matsumotoyoshio.net
電子メールのタイトル:第2回20190427神戸SQL Serverラボに参加します
電子メールの本文:お名前、所属をお書きください。
connpassでの申し込み:https://connpass.com/event/130009/
運営費:500円

(定期開催の予定)

第1回 2019年4月27日(土)18:00~19:30(終了しました)
「SQL Serverのインストールと管理ツールManagement Studioの基本操作」

第2回 2019年5月18日(土)18:00~19:30
「テーブル作成、データ型、主キー、テーブル作成とSQL文」

第3回 2019年6月15日(土)18:00~19:30
「ビューによるデータ操作、射影、選択、結合」

第4回 2019年7月20日(土)18:00~19:30
「関数によるデータ処理、ユーザー定義関数の作成と利用」

第5回 2019年8月17日(土)18:00~19:30
「トリガによるデータ処理の自動化」

第6回 2019年9月21日(土)18:00~19:30
「ストアドプロシージャの作成と利用」

※第7回以降の予定は決定次第ここにご案内します。

2019年5月1日

松本 吉生(まつもとよしお)

Microsoft MVP Data Platform
京都に生まれ、神戸で幼少期を過ごす。大学で応用化学を学んだのち、理科教諭として高等学校に勤務する。教育の情報化が進む中で校内ネットワークの構築運用に従事し、兵庫県立明石高等学校で文部科学省の「光ファイバー網による学校ネットワーク活用方法研究開発事業」に携わる。兵庫県立西宮香風高等学校では多部制単位制の複雑な教育システムを管理する学籍管理データベースシステムをSQL ServerとInfoPath、AccessなどのOfficeソフトウエアによるOBA開発で構築・運用する。2015年から2017年まで兵庫県立神戸工業高等学校でC#プログラミング、IoTなどのコンピュータ教育を行い、現在は兵庫県立神戸甲北高等学校に勤務する。2004年からマイクロソフトMVP(Microsoft Most Valuable Professional)を受賞し、現在15回目の連続受賞。2016年にマイクロソフト認定教育者(Microsoft Innovative Educator Experts : MIEE)を受賞し、現在4回目の連続受賞。

広告

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することによって明示的に値を与えることができ、これはデータの管理の手法として知っておくとよい。