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, specify a primary key column that is not the partition key as 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 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 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 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 RT_PK in PutRow, UpdateRow, or BatchWriteRow.

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

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 table contains the following primary key columns: the Pk1 primary key column of the STRING type and the Pk2_AutoIncrement primary key column of the INTEGER type. The Pk1 primary key column is the partition key and the Pk2_AutoIncrement primary key column is an auto-increment primary key column.

using System;
using System.Collections.Generic;
using Aliyun.OTS.DataModel;
using Aliyun.OTS.Request;
using Aliyun.OTS.Response;

namespace Aliyun.OTS.Samples.Samples
{
    /// <summary>
    /// Example: Create an auto-increment primary key column. 
    /// </summary>
    public class AutoIncrementSample
    {
        private static readonly string TableName = "AutoIncrementSample";

        private static readonly string Pk1 = "Pk1";
        private static readonly string Pk2 = "Pk2_AutoIncrement";


        static void Main(string[] args)
        {
            Console.WriteLine("AutoIncrementSample");

            // Create a table that contains an auto-increment primary key column. 
            CreateTableWithAutoIncrementPk();

            // Write 10 rows of data. 
            for (int i = 0; i < 10; i++)
            {  
                PutRow(i.ToString());
            }

            Console.ReadLine();

        }

        /// <summary>
        /// Create a table that contains an auto-increment primary key column. 
        /// </summary>
        private static void CreateTableWithAutoIncrementPk()
        {

            OTSClient otsClient = Config.GetClient();

            IList<string> tables = otsClient.ListTable(new ListTableRequest()).TableNames;
            if (tables.Contains(TableName))
            {
                return;
            }

            PrimaryKeySchema primaryKeySchema = new PrimaryKeySchema
            {
                { Pk1, ColumnValueType.String },
                // Specify Pk2 as an auto-increment primary key column. 
                { Pk2, ColumnValueType.Integer, PrimaryKeyOption.AUTO_INCREMENT}
            };
            TableMeta tableMeta = new TableMeta(TableName, primaryKeySchema);

            CapacityUnit reservedThroughput = new CapacityUnit(0, 0);
            CreateTableRequest request = new CreateTableRequest(tableMeta, reservedThroughput);
            otsClient.CreateTable(request);

        }

        public static void PutRow(string pk1Value)
        {
            Console.WriteLine("Start put row...");
            OTSClient otsClient = Config.GetClient();

            // Specify the primary key of the row. The primary key must be the same as the primary key that is specified in TableMeta when the table is created. 
            PrimaryKey primaryKey = new PrimaryKey
            {
                { Pk1, new ColumnValue(pk1Value) },
                { Pk2,  ColumnValue.AUTO_INCREMENT }
            };

            // Specify the attribute columns of the row. 
            AttributeColumns attribute = new AttributeColumns
            {
                { "Col1", new ColumnValue(0) }
            };
            PutRowRequest request = new PutRowRequest(TableName, new Condition(RowExistenceExpectation.IGNORE), primaryKey, attribute);
            request.RowPutChange.ReturnType = ReturnType.RT_PK;

            var response =  otsClient.PutRow(request);
            Console.WriteLine("Put row succeed,autoIncrement Pk value:"+ response.Row.GetPrimaryKey()[Pk2].IntegerValue);
        }

    }
}