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.

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 RT_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 RT_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 PK_AUTO_INCR.

    from tablestore import *
    
    table_name = 'OTSPkAutoIncrSimpleExample'
    
    def create_table(client):
        # Create a table that contains the following two primary key columns: gid of the INTEGER type and uid of the INTEGER type, and specify the uid primary key column as the 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 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 PK_AUTO_INCR.

    from tablestore import *
    
    table_name = 'OTSPkAutoIncrSimpleExample'
    
    def put_row(client):
        # Write data to the primary key columns. Set the value of gid to 1 and specify uid as the auto-increment primary key column. You must specify uid as the auto-increment primary key column. Otherwise, an error is returned. 
        primary_key = [('gid',1), ('uid', PK_AUTO_INCR)]
        attribute_columns = [('name','John'), ('mobile',13900006666), ('address','China'), ('age',20)]
        row = Row(primary_key, attribute_columns)
    
        # Write data to attribute columns. 
        row.attribute_columns = [('name','John'), ('mobile',13900006666), ('address','China'), ('age',25)]
        consumed, return_row = client.put_row(table_name, row)
        print ('Write succeed, consume %s write cu.' % consumed.write)
    
        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)