Tablestore:Configure an auto-increment primary key column

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 Initialize an OTSClient instance.

Configure an auto-increment primary key column

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

   You can specify a primary key column only of the INTEGER type 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 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 read data from the table, 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.

   When you read data from the data table, if the complete primary key is recorded, you can read the data based on the primary key. For more information, see Read a single row of data and Read multiple rows of data at a time. If the values of the auto-increment primary key column are not recorded, you must determine the range of data based on the values of the first primary key column and then read the data. For more information, see Read data whose primary key values are within a specific range.

   Note

   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.

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 must set the attribute of the primary key column to AUTO_INCR.

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

   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 set the second primary key column to 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 a value of the auto-increment column. You need only to set the placeholder value AUTO_INCR for the column.

   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]     // Set the second primary key column to 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;
   }