Tablestore auto-increment primary key column - Tablestore

Features

An auto-increment primary key column has the following features:

The values of an auto-increment column are unique and strictly increasing within a partition key, but are not guaranteed to be consecutive.
The data type of an auto-increment column is a 64-bit signed integer.
Auto-increment columns are defined at the data table level. An instance can contain data tables both with and without auto-increment columns.

Note

This feature does not affect the rules for conditional updates. For more information, see Conditional update.

Usage notes

Each data table can have at most one auto-increment primary key column. A partition key cannot be defined as an auto-increment column.
You can define an auto-increment column only when you create a data table. You cannot add one to an existing data table.
Only a primary key column of an integer type can be defined as an auto-increment column. The system-generated value for an auto-increment column is a 64-bit signed integer.
You cannot define an attribute column as an auto-increment column.
The auto-increment primary key column feature and the local transaction feature cannot be used together.
When writing to a table with an auto-increment primary key column, you must retrieve and store the column's generated value for subsequent updates or reads.

API operations

This table describes the API operations related to the auto-increment primary key column feature.

API	Description
CreateTable	To use this feature, you must define a primary key column that is not a partition key as an auto-increment column when you create the data table.
UpdateTable	After a data table is created, you cannot use the UpdateTable API operation to redefine a primary key column as an auto-increment column.
PutRow	When writing data, you do not need to specify a value for the auto-increment column because Tablestore generates one automatically. To obtain the complete primary key, set the ReturnType parameter to RT_PK. You can then use the primary key to query data using the GetRow API operation. Important If you need to update a row but have not stored its auto-incremented primary key value, first use the GetRange API operation to retrieve the full primary key before performing the update.
UpdateRow
BatchWriteRow
GetRow	The GetRow API operation requires a complete primary key. You can obtain the complete primary key by setting the ReturnType parameter to RT_PK in your PutRow, UpdateRow, or BatchWriteRow request. Important If you do not store the value of the auto-increment primary key column when writing data, you can still read a range of data based on the first primary key column. For more information, see Read a range of data.
BatchGetRow

How to use

Console

Create a data table with an auto-increment primary key column.
1. Log on to the Tablestore console.
2. On the Overview page, find the instance that you want to manage and click Manage Instance in the Actions column.
3. On the Instance Details tab, in the Tables section, click Create Table.
4. In the Create Table dialog box, configure the Table Name and Primary Key. Configure other parameters based on your business requirements.
  
  When you configure the primary key, select Auto Increment as the type for a primary key column that is not a partition key.
  
  Note
  For more information about parameter configurations, see Data table operations.
5. Click Create.
Write data to the table.
1. On the Instance Details tab, in the Tables section, click the name of the data table.
2. On the Query Data tab, click Insert.
3. In the Insert dialog box, enter the primary key values and add attribute columns as needed.
  
  You do not need to enter a value for the auto-increment column. The system automatically generates a value when the data is written. To add multiple attribute columns, click the icon repeatedly and configure the name, type, and value for each.
4. Click OK.
  
  The system displays the successfully written data. Record the complete primary key of the row for subsequent updates or reads.

CLI

Create and use a data table with 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 example creates a data table named mytable. This table has two primary key columns: uid (string) and pid (integer), where pid is the auto-increment column. The data in this 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 data table.
Run the put command to write a row. For more information, see Insert new data.

Note
After writing data, run the scan command to retrieve a row and obtain its complete primary key. For more information, see Scan data or Export data.

The following example inserts a row where the first primary key column's value is "86" and the auto-increment column is set to null. The row also includes two attribute columns: name (string) and country (string).
```
put --pk '["86", null]' --attr '[{"c":"name", "v":"redchen"}, {"c":"country", "v":"china"}]'
```

SDKs

This feature is available in the Java, Go, Python, Node.js, .NET, and PHP SDKs. This section provides an example that uses the Java SDK.

The following example writes a row to example_table and retrieves the generated value of the incr column from the response.

PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("partition-a"));
primaryKeyBuilder.addPrimaryKeyColumn("incr", PrimaryKeyValue.AUTO_INCREMENT);
PrimaryKey primaryKey = primaryKeyBuilder.build();

RowPutChange rowPutChange = new RowPutChange("example_table", primaryKey);
rowPutChange.addColumn("payload", ColumnValue.fromString("example-value"));
rowPutChange.setReturnType(ReturnType.RT_PK);

PutRowResponse response = client.putRow(new PutRowRequest(rowPutChange));
long generatedValue = response.getRow().getPrimaryKey()
        .getPrimaryKeyColumn("incr").getValue().asLong();
System.out.println("Generated value: " + generatedValue);

Billing

This feature does not affect existing billing rules. Returning the primary key data consumes no additional read capacity units.