All Products
Search
Document Center

Tablestore:Configure an auto-increment primary key column

Last Updated:Jul 01, 2024

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. Tablestore automatically generates values for the auto-increment primary key column. The values generated for the auto-increment primary key column are unique and increase monotonically within a partition.

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, 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 a table that contains an auto-increment primary key column, 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.

    When you read data from the data table, you can read the data based on the primary key if the complete primary key is recorded. 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 in the specified 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 specify a primary key column that is not the partition key as an auto-increment primary key column when you create a table, you need to only set the attribute of the primary key column to PK_AUTO_INCR.

    The following sample code provides an example on how to create a data table that contains an auto-increment primary key column. In this example, the data table contains two primary key columns: gid of the INTEGER type and uid of the INTEGER type. The gid column is the partition key and the uid column is an auto-increment primary key column.

    table_name = '<TABLE_NAME>'
    def create_table(client):
        # Create a table that contains the following primary key columns: gid of the INTEGER type and uid of the INTEGER type. Then, specify the uid primary key column as an auto-increment primary key column. 
        schema_of_primary_key = [('gid', 'INTEGER'), ('uid', 'INTEGER', PK_AUTO_INCR)]
        table_meta = TableMeta(table_name, schema_of_primary_key)
        table_options = TableOptions()
        reserved_throughput = ReservedThroughput(CapacityUnit(0, 0))
        client.create_table(table_meta, table_options, reserved_throughput)
        print ('Table has been created.')
  2. Write data

    When 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. You need to only set the attribute of the primary key column to PK_AUTO_INCR.

    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.

    table_name = '<TABLE_NAME>'
    def put_row(client):
        # Specify the primary key information about the row that you want to write. In this example, the gid column is set to 1 and the uid column is an auto-increment primary key column. You must set uid to PK_AUTO_INCR. Otherwise, an error is returned. 
        primary_key = [('gid',1), ('uid', PK_AUTO_INCR)]
        # Specify the attribute columns of the row that you want to write. 
        attribute_columns = [('name','John'), ('mobile',1390000****), ('address','China'), ('age',20)]
        row = Row(primary_key, attribute_columns)
    
        # Write data and return the number of consumed CUs, but do not return the primary key information. 
        #consumed, return_row = client.put_row(table_name, row)
        #print ('Write succeed, consume %s write cu.' % consumed.write)
        
        # Write data and return the number of consumed CUs and the primary key information. 
        consumed, return_row = client.put_row(table_name, row, return_type = ReturnType.RT_PK)
        print ('Write succeed, consume %s write cu.' % consumed.write)
        print ('Primary key:%s' % return_row.primary_key)