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 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 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 that shares the same partition key value.

Note Tablestore SDK for PHP V4.0.0 or later allows you to configure auto-increment primary key columns.

Prerequisites

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

Configure an auto-increment primary key column

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

    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.

  2. When you write data to a 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 ReturnType to ReturnTypeConst::CONST_PK.

    When you query data, you must specify the values of all primary key columns. To obtain a complete primary key value, you can set ReturnType to ReturnTypeConst::CONST_PK in PutRow, UpdateRow, or BatchWriteRow.

    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

You can use the auto-increment primary key column feature when you call the CreateTable, PutRow, UpdateRow, or BatchWriteRow operation.

  1. Create a table

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

    function createTable($otsClient) 
    {
        $request = [
            'table_meta' => [
                'table_name' => 'table_name',       // Specify the name of the table. 
                'primary_key_schema' => [
                    ['PK_1', PrimaryKeyTypeConst::CONST_STRING],    // Set the name of the first primary key column to PK_1 and the type of the first primary key column to STRING. The first primary key column is the partition key. 
                    ['PK_2', PrimaryKeyTypeConst::CONST_INTEGER, PrimaryKeyOptionConst::CONST_PK_AUTO_INCR]
                    // Set the name of the second primary key column to PK_2 and the type of the second primary key column to INTEGER and specify the second primary key column as the auto-increment primary key column. 
                ]
            ],
            'reserved_throughput' => [
                'capacity_unit' => [         // Set both the reserved read throughput and the reserved write throughput to 0 CUs. 
                    'read' => 0,
                    'write' => 0
                ]
            ],
            'table_options' => [
                'time_to_live' => -1,             // Specify that data never expires. 
                'max_versions' => 1,              // Specify that only one version of data in each attribute column is retained. 
                'deviation_cell_version_in_sec' => 86400   // Specify the max version offset of data. Unit: seconds. 
            ]
        ];
        $otsClient->createTable($request);
    }
                        
  2. Write data to a table

    When you write data to a 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 PrimaryKeyTypeConst::CONST_PK_AUTO_INCR.

    function putRow($otsClient)
    {
        $row = [
            'table_name' => 'table_name',
            'primary_key' => [
                ['PK_1', 'Hangzhou'],                      // Specify the name and value of the first primary key column in the list format. 
                ['PK_2', null, PrimaryKeyTypeConst::CONST_PK_AUTO_INCR]     // Specify the second primary key column as the auto-increment primary key column. You do not need to specify the value. Instead, you need to only set the value of the auto-increment primary key column to PrimaryKeyTypeConst::CONST_PK_AUTO_INCR. Tablestore automatically generates the value. 
            ],
            'attribute_columns' => [              // Specify attribute columns in the list format. 
                ['name', 'John'],                  // [The attribute column name, attribute column value, attribute column type, and timestamp]. Ignore the parameters that are not specified. 
                ['age', 20],
                ['address', 'Alibaba'],
                ['product', 'OTS'],
                ['married', false]
            ],
            'return_content' => [
                'return_type' => ReturnTypeConst::CONST_PK     // Set return_type to ReturnTypeConst::CONST_PK to return the value of the auto-increment primary key column. 
            ]
        ];
        $ret = $otsClient->putRow($row);
        print_r($ret);
    
        $primaryKey = $ret['primary_key'];    // The obtained primary key value can be passed to operations such as GetRow, UpdateRow, and DeleteRow. 
        return $primaryKey;
    }