All Products
Search
Document Center

Tablestore:Configure an auto-increment primary key column

Last Updated:Nov 14, 2023

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.

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 rule of conditional update. For more information about conditional update, see Conditional update.

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 column as the auto-increment primary key column. If you specify the partition key column as the auto-increment primary key column, values in the column cannot be automatically generated.

UpdateTable

After a data table is created, you cannot call the UpdateTable operation to specify a primary key column as the auto-increment primary key column.

PutRow

When you write data to a table, Tablestore automatically generates a value for the auto-increment primary key column.

You can set ReturnType to RT_PK to obtain values of all primary key columns and use the values to call the GetRow operation to query data.

Important

If you want to update an existing row but you do not record the values of the auto-increment primary key column, call the GetRange operation to obtain the primary key information about the row before you update.

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 ReturnType to RT_PK in the PutRow, UpdateRow, or BatchWriteRow operation.

Important

If you want to write data but you do not record the values of an auto-increment primary key column, you can call the GetRange operation to read data whose primary key is in the specified range. For more information, see Read data.

BatchGetRow

Procedures

Use the Tablestore console

  1. 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 about how to configure the parameters, see the "Create a data table" section of the Use the Wide Column model in the Tablestore console topic

    5. In the message that appears, click OK.

  2. 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 the value of the auto-increment primary key column. You can add multiple attribute columns by clicking Add column and configuring the Name, Type, Value, and Version 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

  1. 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 the "Create a table" section of the Operations on data tables topic.

      In the following example, a data table named mytable with an auto-increment primary key column is created. This data table has a primary key of the STRING type named uid and a primary key of the INTEGER type named pid. The pid primary key is configured as the auto-increment primary key. 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 data table.

  2. Run the put command to write a row of data. For more information, see the "Insert a row of data" section of the Operations on data topic.

    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 the "Scan data" section of the Operations on data topic or the "Export data" section of the Operations on data topic.

    In the following example, a row of data is inserted. 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 the 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 and Tablestore SDK for PHP to use the auto-increment primary key column feature. In this example, Tablestore SDK for Java is used to describe how to use Tablestore SDKs to configure an auto-increment primary key column.

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

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