SQL CLIからのIDENTITY値の挿入

DDL文およびAPIコールを使用して、GENERATED ALWAYSまたはGENERATED BY DEFAULTのどちらで指定したかに関係なく、値をIDENTITYフィールドに挿入できます。

作成する各IDENTITYフィールドでは、次のいずれかの構文が使用されます。

GENERATED ALWAYS AS IDENTITY
GENERATED BY DEFAULT AS IDENTITY
GENERATED BY DEFAULT ON NULL AS IDENTITY

IDENTITYフィールドの作成方法は、値をINSERTするときの動作に影響します。主キーである列のIDENTITY値は変更できません。

たとえば、IDENTITYフィールドがGENERATED ALWAYSである次の表を作成します。IDENTITYフィールドは主キーではありません。


sql-> CREATE Table Test_SGSqlInsert2(
  id INTEGER,
  name STRING,
  deptId INTEGER GENERATED ALWAYS AS IDENTITY (CACHE 1),
  PRIMARY KEY(id));
Statement completed successfully

この表に値を正常に挿入するには、deptID IDENTITYフィールドの値としてDEFAULTを常に指定して、SGが次の値を生成するようにします。


sql->
> INSERT INTO Test_SGSqlInsert2 VALUES (148, 'sally', DEFAULT);
> INSERT INTO Test_SGSqlInsert2 VALUES (250, 'joe', DEFAULT);
> INSERT INTO Test_SGSqlInsert2 VALUES (346, 'dave', DEFAULT);
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned

前のINSERT文によって次の行が追加され、DEFAULTを指定するたびに、SGが次に使用可能なIDENTITY値を取得します。


sql-> select * from Test_SGSqlInsert2;
{"id":148,"name":"sally","deptId":1}
{"id":250,"name":"joe","deptId":2}
{"id":346,"name":"dave","deptId":3}
3 rows returned

1つの文で将来参照するために生成されたdeptIdの値を取得するには、returning deptId句を次のように使用します。

 
INSERT INTO Test_SGSqlInsert2 VALUES (600, 'jabba', DEFAULT) returning deptId;
{"deptId":6} 
INSERT INTO Test_SGSqlInsert2 VALUES (700, 'bubba', DEFAULT) returning deptId;
{"deptId":7}

次のINSERT文を使用して、DEFAULTではなく値を指定すると、例外が発生します。GENERATED BY DEFAULT AS IDENTITYとして定義するIDENTITYフィールドに値を指定することはできません。

 
sql-> INSERT INTO Test_SGSqlInsert2 VALUES (1, 'joe', 200) ;
Error handling command INSERT INTO Test_SGSqlInsert2 VALUES (1, 'joe', 200): 
Error: at (1, 48) Generated always identity column must use DEFAULT construct.

別の例として、DeptID整数フィールド(GENERATED BY DEFAULT AS IDENTITY)を持つ表を作成し、このフィールドを主キーおよびシャード・キーにします。


sql-> CREATE TABLE Test_SGSqlInsert_Default (
>  ID INTEGER,
>  NAME STRING,
>  DeptID INTEGER GENERATED BY DEFAULT AS IDENTITY (
>  START WITH 1
>  INCREMENT BY 1
>  MAXVALUE 100),
>  PRIMARY KEY (SHARD(DeptID), ID));
Statement completed successfully

次の文は、表Test_SGSqlInsert_Defaultに値を挿入する方法を示しています。この場合、列IDがIDENTITYではないため、整数値をフィールドに割り当てることができます。


sql->
> INSERT INTO Test_SGSqlInsert_Default VALUES (100, 'tim', DEFAULT);
> INSERT INTO Test_SGSqlInsert_Default VALUES (200, 'dave', 210);
> INSERT INTO Test_SGSqlInsert_Default VALUES (300, 'sam', 310);
> INSERT INTO Test_SGSqlInsert_Default VALUES (400, 'Jennifer', DEFAULT);
> INSERT INTO Test_SGSqlInsert_Default VALUES (500, 'Barbara', 2);
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
1 row returned

これらのサンプル文により、データベースに次の行が挿入されます。


sql-> select * from Test_SGSqlInsert_Default;
{"ID":300,"NAME":"sam","DeptID":310}
{"ID":100,"NAME":"tim","DeptID":1}
{"ID":400,"NAME":"Jennifer","DeptID":2}
{"ID":500,"NAME":"Barbara","DeptID":2}
{"ID":200,"NAME":"dave","DeptID":210}
5 rows returned

INSERT文で2つの値をDEFAULTとして指定したため、SGによってこれらが1および2として指定されます。他の値は指定したとおりに挿入されます(210、310および2)。各値は、2つのDeptID値が同じになる場合でも受入れ可能です(DEFAULTから提供される2と、指定した値の2)。

IDENTITY列がGENERATED BY DEFAULT AS IDENTITYとして定義されているため、値を指定しない場合のみ、SGによって値が提供されます。値210、310または2は正しい指定です。重複はチェックされず、GENERATED BY DEFAULT AS IDENTITY列の値が一意であることも強制されません。重複する値が存在しないことが要件である場合、これはアプリケーションによって確認されます。