IDENTITY列


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

 IMG_0440

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

<本日の題材>
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;

Identity_確認

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

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

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

Identity_error

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

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

identity_set

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

Identity_確認2

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

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

identity_setoff

 結果を確認すると、

Identity_確認3

先ほどの受注番号の最大値「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のときと同様に、データを登録すると、

ora_identity_insert

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

ora_identity_確認

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

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

ora_ident_update_err

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

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

ora_ident_rollback]

再度登録します。
ora_identity_insert2

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

ora_identity_確認2

そうすると、受注番号は「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 (受注番号));

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

ora_identity_insert3

データを確認します。

ora_identity_確認3

今度は、受注番号は、100からの連番で作成されているのが確認できます。
受注番号を更新してみます。

UPDATE 受注TBL SET
  受注番号 = 200
 WHERE 受注番号 = 100;

ora_ident_update

 今回は、更新できることが確認できました。
再度データを確認します。

ora_identity_確認4

 Oracleの場合、このIDENTITY列の実体は、SEQUENCEオブジェクトとDEFAULTの組合せとのことです。「USER_OBJECTS」の内容を確認してみます。

SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE
  FROM USER_OBJECTS;

ora_object

SEQUENCEが作成されているのが確認できます。

 今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>