Previous Next JavaScript must be enabled to correctly display this content

## 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-9 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)

Output:

    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-10 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-11 Inserting rows into a table with primary key as IDENTITY Column GENERATED BY DEFAULT

    CREATE TABLE Test_SGSqlInsert_Default_id (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        name STRING,
        deptId INTEGER,
    PRIMARY KEY (id))

    INSERT INTO Test_SGSqlInsert_Default_id VALUES (100, 'tim', 3)
    INSERT INTO Test_SGSqlInsert_Default_id VALUES (DEFAULT, 'dave', 210)
    INSERT INTO Test_SGSqlInsert_Default_id VALUES (300, 'sam', 310)
    INSERT INTO Test_SGSqlInsert_Default_id VALUES (500, 'Jennifer', 410)
    INSERT INTO Test_SGSqlInsert_Default_id (name,deptId) VALUES ("Abby", 510)

The above statements will insert the following rows into the database. Notice that when you supply DEFAULT for the `id` field, the SG auto-generates an `id` value because the primary key field (`id`) is defined as the IDENTITY column. Similarly, in the last insert statement where you supply only the `name` and `deptID` field values, the SG auto-generates the `id` value.

    {"id":2,"name":"Abby","deptId":4}
    {"id":300,"name":"sam","deptId":310}
    {"id":100,"name":"tim","deptId":3}
    {"id":1,"name":"dave","deptId":210}
    {"id":500,"name":"Jennifer","deptId":410}