All Products
Search
Document Center

Tablestore:Configure an auto-increment primary key column

Last Updated:Nov 10, 2023

This topic describes how to configure an auto-increment primary key column. You can specify a primary key column that is not the partition key as an 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 because 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.

Important

Tablestore SDK for Java V4.2.0 and later allow you to configure auto-increment primary key columns.

Usage notes

When you write data to a table that contains an auto-increment primary key column, you must configure the system to return the values generated for the auto-increment primary key column and record the values for subsequent data updates or data reading.

Prerequisites

An OTSClient instance is initialized. For more information, see Initialization.

Procedure

  1. When you create a table, specify a primary key column that is not the partition key as an auto-increment primary key column.

    Only a primary key column of the INTEGER type can be specified as an auto-increment primary key column. Each value generated for an auto-increment primary key column is a 64-bit signed integer of the LONG type.

  2. 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 placeholders.

    If you want to obtain the values of the auto-increment primary key column after data is written to the table, you can set the ReturnType parameter to RT_PK.

    When you read data from the table, you must specify the values of all primary key columns. 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.

    If you have recorded the values of all primary key columns, you can call the GetRow or BatchGetRow operation and specify the values of all primary key columns to read one or more rows of data. If you have not recorded the values of the auto-increment primary key column, you can call the GetRange operation to read data whose values of the first primary key column are within a specific range.

    Note

    If you want to update an existing row, call the GetRange operation to obtain the primary key information about the row before you update the data.

Examples

  1. 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);
    }
  2. 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());
    }