Inserting Rows with an IDENTITY Column

The system generates an IDENTITY column value when the keyword DEFAULT is used as the insert_clause for the IDENTITY column. Here are a few examples that show INSERT statements for both flavors of the IDENTITY column – GENERATED BY DEFAULT and GENERATED ALWAYS.

How you create an IDENTITY field affects what happens when you INSERT values. You cannot change the IDENTITY value of a column that is a primary key.

Example 7-1 Create a table with an IDENTITY field as GENERATED ALWAYS Where the IDENTITY field is not a primary key

CREATE Table Test_SGSqlInsert2(
    id INTEGER,
    name STRING,
    deptId INTEGER GENERATED ALWAYS AS IDENTITY (CACHE 1),
PRIMARY KEY(id));

INSERT INTO Test_SGSqlInsert2 VALUES (148, 'sally', DEFAULT);
INSERT INTO Test_SGSqlInsert2 VALUES (250, 'joe', DEFAULT);
INSERT INTO Test_SGSqlInsert2 VALUES (346, 'dave', DEFAULT);

The above INSERT statement will insert the following rows. The system generates values 1, 2, and 3 for IDENTITY column deptId.

148, 'sally', 1
250, 'joe', 2
346, 'dave', 3

To get the value of the generated deptId for future reference in one statement, use the returning deptId clause as follows:

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

Using the following INSERT statement causes an exception since the user supplied a value of 200 for an IDENTITY GENERATED ALWAYS column. You cannot specify any value for any IDENTITY field you define as GENERATED BY DEFAULT AS IDENTITY.

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.

Example 7-2 Create a table with a DeptID integer field, GENERATED BY DEFAULT AS IDENTITY, and make it the primary and shard key

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));

The following statements show how to insert values into table Test_SGSqlInsert_Default. In this case, since the column ID is not an IDENTITY, you can assign integer values to the field:

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);

These sample statements insert the following rows into the database.

300, 'sam', 310
100, 'tim', 1
400, 'Jennifer', 2
500, 'Barbara', 2
200, 'dave', 210

Since you specified two values as DEFAULT in your INSERT statements, the SG supplies them, as 1 and 2. The other values are inserted as you specify (210, 310, and 2). Each value is acceptable, even though one results in two DeptID values the same (2 supplied from a DEFAULT, and 2 as a value you supply).

Because you defined the IDENTITY column as GENERATED BY DEFAULT AS IDENTITY, the SG supplies a value only when you do not specify a value. Specifying values 210, 310, or 2 is correct. The system neither checks for duplicates, nor enforces uniqueness for GENERATED BY DEFAULT AS IDENTITY column values. It is the application’s responsibility to ensure that there are no duplicate values if that is a requirement.

Example 7-3 Inserting rows into a table with an IDENTITY Column with GENERATED BY DEFAULT

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));

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);

The above statements will insert the following rows into the database. Notice that user supplied values 210, 310 and 2 are accepted because the IDENTITY column is defined as GENERATED BY DEFAULT. Note that IDENTITY column value 2 is a duplicate; the system does not check for duplicates or enforce uniqueness of the GENERATED BY DEFAULT IDENTITY column values. It is the application’s responsibility to ensure that there are no duplicate values.

100, 'tim', 1
200, 'dave', 210
300, 'sam', 310
400, 'Jennifer', 2
500, 'Barbara', 2