Use SDKs to configure an auto-increment primary key column for a data table - Tablestore

You can specify a primary key column that is not the partition key as the auto-increment primary key column. If you write data to a table that contains an auto-increment primary key column, you do not need to specify values for the auto-increment primary key column. Tablestore automatically generates values for the auto-increment primary key column. Values generated for the auto-increment primary key column are unique and increase monotonically within a partition.

Characteristics

The auto-increment primary key column feature has the following characteristics:

The values of an auto-increment primary key column are unique and increase monotonically but not always continuously within a partition that shares the same partition key value.
The values of an auto-increment primary key column are 64-bit signed integers of the LONG data type.
You can create an auto-increment primary key column for a data table. An instance can include data tables that contain auto-increment primary key columns and data tables that do not contain auto-increment primary key columns.

Note

The auto-increment primary key column feature does not affect the conditions that you specify for conditional update. For more information, see Conditional updates.

Usage notes

You can specify up to one auto-increment primary key column for a data table. You cannot specify the partition key column as the auto-increment primary key column.
You can specify an auto-increment primary key column only when you create a data table. You cannot specify an auto-increment primary key column for an existing data table.
You can specify a primary key column only of the INTEGER type as the auto-increment primary key column. Each value generated for an auto-increment primary key column is a 64-bit signed integer of the LONG data type.
You cannot specify an attribute column as the auto-increment primary key column.
You cannot use the auto-increment primary key column feature and the local transaction feature at the same time.
If you write data to a table that contains an auto-increment primary key column, you must return and record the values of the auto-increment primary key column for subsequent data updates and data read.

API operations

The following table describes the API operations that are related to the auto-increment primary key column feature.

API operation	Description
CreateTable	When you create a data table, you can specify a primary key column that is not the partition key as an auto-increment primary key column. If you specify the partition key as an auto-increment primary key column, values cannot be automatically generated for the column.
UpdateTable	After a data table is created, you cannot call the UpdateTable operation to specify a primary key column as an auto-increment primary key column.
PutRow	When you write data to a table that contains an auto-increment primary key column, Tablestore automatically generates values for the auto-increment primary key column. You can set the ReturnType parameter to RT_PK to obtain the values of all primary key columns, and use the values when you call the GetRow operation to query data. Important If you want to update an existing row but you do not record the value of the auto-increment primary key column, call the GetRange operation to obtain the primary key information of the row before you update data.
UpdateRow
BatchWriteRow
GetRow	You must use the values of all primary key columns when you call the GetRow operation. To obtain the values of all primary key columns, you can set the ReturnType parameter to RT_PK when you call the PutRow, UpdateRow, or BatchWriteRow operation. Important If you do not record the values of the auto-increment primary key column when you write data, you can call the GetRange operation to read data based on the range that is specified by the values of the first primary key column. For more information, see Read data whose primary key values are in the specified range.
BatchGetRow

Procedures

Use the Tablestore console

Create a data table that contains an auto-increment primary key column.
1. Log on to the Tablestore console.
2. On the Overview page, find the instance in which you want to create a data table. Click the name of the instance or click Manage Instance in the Actions column.
3. On the Tables tab of the Instance Details tab, click Create Table.
4. In the Create Table dialog box, configure the Table Name and Table Primary Key parameters. Configure other parameters based on your business scenarios.
  Select Auto Increment for a primary key that is not the partition key.
  Note
  For more information, see Step 3: Create a data table.
5. In the message that appears, click OK.
Write data to the table.
1. On the Tables tab of the Instance Details tab, click the name of the table to which you want to write data.
2. On the Query Data tab, click Insert.
3. In the Insert dialog box, enter a primary key value and add an attribute column based on your requirements.
  Tablestore automatically generates values for the auto-increment primary key column. You can add multiple attribute columns. Each time you add an attribute column, you must click the icon and configure the Name, Type, and Value parameters.
4. In the message that appears, click OK.
  The written data is displayed on the Query Data tab. Record the values of all primary key columns for subsequent data updates and data read.

Use the Tablestore CLI

Create a data table that contains an auto-increment primary key column.
1. Run the create command to create a data table. For more information, see Create a table.
  The following sample code provides an example on how to create a data table named mytable that contains an auto-increment primary key column. This table has a primary key column of the STRING type named uid and a primary key column of the INTEGER type named pid. The pid column is an auto-increment primary key column. Data in the table never expires.
```
create -t mytable --pk '[{"c":"uid", "t":"string"}, {"c":"pid", "t":"integer", "opt":"auto"}]'
```
2. Run the use --wc -t mytable command to use the table.
Run the put command to write a row of data. For more information, see Insert data.
Note
After you write data, you can run the scan command to obtain the values of all primary key columns of the specified row. For more information, see Scan data and Export data.
The following sample code provides an example on how to write a row of data: The value of the first primary key column in the row is 86. The value of the second primary key column in the row is null. The row contains two attribute columns: name and country. Both columns are of the STRING type.
```
put --pk '["86", null]' --attr '[{"c":"name", "v":"redchen"}, {"c":"country", "v":"china"}]'
```

Use Tablestore SDKs

You can use Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, or Tablestore SDK for PHP to use the auto-increment primary key column feature. In this example, Tablestore SDK for Java is used.

Create a table that contains an auto-increment primary key column.

To create an auto-increment primary key column when you create a table, you need to only set the attribute of a primary key column to AUTO_INCREMENT.

The following sample code provides an example on how to create a table that contains an auto-increment primary key column. The table contains the pk1 primary key column whose data type is STRING and the pk2 primary key column whose data type is INTEGER. The pk1 column is a partition key column, and the pk2 column is an auto-increment primary key column.

private static void createTable(SyncClient client) {
    // Specify the name of the table. 
    TableMeta tableMeta = new TableMeta("<TABLE_NAME>");
    // Create the first primary key column. The first primary key column is a partition key column. 
    tableMeta.addPrimaryKeyColumn(new PrimaryKeySchema("pk1", PrimaryKeyType.STRING));
    // Create the second primary key column whose data type is INTEGER and set the attribute of the column to AUTO_INCREMENT. 
    tableMeta.addPrimaryKeyColumn(new PrimaryKeySchema("pk2", PrimaryKeyType.INTEGER, PrimaryKeyOption.AUTO_INCREMENT));
    // Specify the time to live (TTL) of data. A value of -1 specifies that the data never expires. Unit: seconds. 
    int timeToLive = -1;  
    // Specify the maximum number of versions that can be retained for each column. A value of 1 specifies that only the latest version is retained for each column. 
    int maxVersions = 1; 
    TableOptions tableOptions = new TableOptions(timeToLive, maxVersions);
    CreateTableRequest request = new CreateTableRequest(tableMeta, tableOptions);
    client.createTable(request);
}

Write data to the table.

When you write data to the table, you do not need to specify values for the auto-increment primary key column. You need to only set the values of the auto-increment primary key column to AUTO_INCREMENT.

The following sample code provides an example on how to write a row of data to the table and return the values of all primary key columns and the reserved read and write capacity units (CUs) that are consumed.

private static void putRow(SyncClient client, String receive_id) {
    // Construct the primary key. 
    PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    // Set the values of the first primary key column to the value of the receive_id parameter whose data type is STRING. 
    primaryKeyBuilder.addPrimaryKeyColumn("pk1", PrimaryKeyValue.fromString(receive_id));
    // The second primary key column is an auto-increment primary key column, and you do not need to specify values for the second primary key column. You need to only set the values of the auto-increment primary key column to AUTO_INCREMENT. Tablestore automatically generates values for the auto-increment primary key column. 
    primaryKeyBuilder.addPrimaryKeyColumn("pk2", PrimaryKeyValue.AUTO_INCREMENT);
    PrimaryKey primaryKey = primaryKeyBuilder.build();
    // Specify the name of the table. 
    RowPutChange rowPutChange = new RowPutChange("<TABLE_NAME>", primaryKey);
    // Set the ReturnType parameter to RT_PK to include the values of all primary key columns in the response. By default, if you do not set the ReturnType parameter to RT_PK, the values of all primary key columns are not returned. 
    rowPutChange.setReturnType(ReturnType.RT_PK);
    // Add attribute columns. 
    rowPutChange.addColumn(new Column("content", ColumnValue.fromString("content")));
    // Write data to the table. 
    PutRowResponse response = client.putRow(new PutRowRequest(rowPutChange));
    // Display the values of all primary key columns. 
    Row returnRow = response.getRow();
    if (returnRow != null) {
        System.out.println("PrimaryKey:" + returnRow.getPrimaryKey().toString());
    }
    // Display the consumed CUs. 
    CapacityUnit  cu = response.getConsumedCapacity().getCapacityUnit();
    System.out.println("Read CapacityUnit:" + cu.getReadCapacityUnit());
    System.out.println("Write CapacityUnit:" + cu.getWriteCapacityUnit());
}

Billing

When you use the auto-increment primary key column feature, the existing billing rules are not affected. The primary key values that are returned do not consume additional read capacity units (CUs).