IDENTITY列

ITコーディネータのシュウです。

社長がアメリカのサンフランシスコにあるニューコムの支社に行ったついでに、シリコンバレーに寄った際に撮ってきたオラクル社の写真です。誰かが言ってましたが、丸いビルと模様はハードディスクを意味しているとか！　本当ですかね？　ただ、たしかにシリンダーみたいですよね。
グーグルやアップル、インテル、ヒューレットパッカード、シスコシステムズ、ヤフー、アドビ、イーベイなど、シリコンバレーにある著名な企業を上げればきりがないほどですが、シリコンバレーっていうのは、実際にシリコンバレーという都市が存在しているわけではなく、サンノゼ、マウンテンビュー、サニーベール、サンタクララといった複数の市からなる場所だそうです。

＜本日の題材＞
IDENTITY列

前回、SEQUENCEについて取り上げましたが、今回はSQL Serverではよく使用されていたIDENTITYプロパティが、Oracle 12cから使用できるようになったという件を取り上げてみたいと思います。

SQL ServerのIDENTITY プロパティというのは、Accessの「オートナンバー型」のように、ある列の値を自動的に連番が設定されるようにしたい場合に使用します。

前回取り上げたシーケンスが、特定のテーブルとは関連づかない独立したオブジェクトであるのに対して、IDENTITYのほうは、特定のテーブルの列に関連づく機能というところが違います。

★例）SQL Serverの場合：

CREATE TABLE dbo.受注TBL(
受注番号 INT IDENTITY(1,1)
,受注日 DATE
,顧客番号 VARCHAR(10)
,決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

上記は、受注番号に IDENTITYプロパティを設定し、最初の値を「1」、増分値を「1」としています。

データを登録する場合は、以下のように、IDENTITYプロパティを設定した項目を除いて設定します。

INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
VALUES('2015-08-24','K0001051',34000);

INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
VALUES('2015-08-24','K0001078',42000);

結果を確認してみます。
SELECT * FROM dbo.受注TBL;

たしかに、受注番号は「1」から順に作成されています。

INSERT文にこのIDENTITYプロパティを設定した項目についても含めてしまうと、下記のようにエラーとなります。

INSERT INTO dbo.受注TBL(受注番号,受注日,顧客番号,決済金額)
VALUES(3, '2015-08-24','K0001078',42000);

このIDENTITYプロパティを設定した項目に対して、どうしても自動での登録ではなく、明示的に設定して登録したい場合には、「SET IDENTITY_INSERT テーブル名 ON」という設定をした後で行う必要があります。

SET IDENTITY_INSERT dbo.受注TBL ON;
INSERT INTO dbo.受注TBL(受注番号,受注日,顧客番号,決済金額)
VALUES(10, '2015-08-24','K0001083',17000);

結果を確認してみます。
SELECT * FROM dbo.受注TBL;

再度設定を元に戻して、登録を行います。

SET IDENTITY_INSERT dbo.受注TBL OFF;
INSERT INTO dbo.受注TBL(受注日,顧客番号,決済金額)
values('2015-08-24','K0001091',56000);

結果を確認すると、

先ほどの受注番号の最大値「10」の後の連番で「11」の行が登録されているのが確認できます。

IDENTITY列というのは上記のようなかたちで使いますが、Oracleでは11gまではサポートされていませんでしたが、12c でこのIDENTITY列がサポートされるようになりました。

★例）Oracle 12c の場合

IDENTITYを設定したい列について、ユーザーが更新することが不可能な列として作成する場合、「GENERATED ALWAYS AS IDENTITY」をつけます。

CREATE TABLE 受注TBL(
受注番号 INT GENERATED ALWAYS AS IDENTITY
, 受注日 DATE
, 顧客番号 VARCHAR(10)
, 決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

SQL Serverのときと同様に、データを登録すると、

結果を確認すると、
SELECT * FROM 受注TBL;

受注番号は、1からの連番で作成されているのが確認できます。

ここで、受注番号を更新してみます。
UPDATE 受注TBL SET
受注番号 = 100
WHERE 受注番号 = 1;

上記のように、更新できない旨のエラーが表示されます。

次に、上記の処理をロールバックします。
ROLLBACK;

]

再度登録します。

結果を確認します。
SELECT * FROM 受注TBL;

そうすると、受注番号は「3」から始まっていて、先ほどのロールバックの処理ではこのIDENTITYの値はロールバックされないことが確認できます。（シーケンスと同様）

次に、このIDENTITYの列の値を更新可能な列として作成する場合には、以下のように「GENERATED BY DEFAULT AS IDENTITY」をつけます。また、連番の最初の値を「100」とします。

CREATE TABLE 受注TBL(
受注番号 INT GENERATED BY DEFAULT AS IDENTITY(START WITH 100)
, 受注日 DATE
, 顧客番号 VARCHAR(10)
, 決済金額 DECIMAL(12)
, CONSTRAINT PK_受注TBL PRIMARY KEY (受注番号));

再度、データを登録します。