To declare GLOBAL AUTOINCREMENT columns in your UltraLite database

1. Assign each copy of the database a unique global ID number.

   The global_database_id database option sets the value in your UltraLite database. When deploying UltraLite, you must assign a different identification number to each database. See UltraLite global_database_id option.

2. Allow UltraLite to supply default values for the column using the partition uniquely identified by the UltraLite database's number. UltraLite follows these rules:

   • If the column contains no values in the current partition, the first default value is pn + 1. p represents the partition size and n represents the global ID number.
   • If the column contains values in the current partition, but all are less than p(n + 1), the next default value will be one greater than the previous maximum value in this range.
   • Default column values are not affected by values in the column outside of the current partition; that is, by numbers less than pn + 1 or greater than p(n + 1). Such values may be present if they have been replicated from another database via MobiLink synchronization.

     For example, if you assigned your UltraLite database a global ID of 1 and the partition size is 1000, then the default values in that database would be chosen in the range 1001-2000. Another copy of the database, assigned the identification number 2, would supply default values for the same column in the range 2001-3000.

   • Because you cannot set the global ID number to negative values, the values UltraLite chooses for GLOBAL AUTOINCREMENT columns are always positive. The maximum identification number is restricted only by the column data type and the partition size.
   • If you do not set a global ID value, or if you exhaust values from the partition, a NULL value is inserted into the column. Should NULL values not be permitted, the attempt to insert the row causes an error.

3. If you exhaust or will soon exhaust available values for columns declared as GLOBAL AUTOINCREMENT, you need to set a new global ID. UltraLite chooses GLOBAL AUTOINCREMENT values from the partition identified by the global ID number, but only until the maximum value is reached. If you exceed values, UltraLite begins to generate NULL values. By assigning a new global ID number, you allow UltraLite to set appropriate values from another partition.

   One method of choosing a new global ID is to maintain a pool of unused global ID values. This pool is maintained in the same manner as a pool of primary keys. See Using primary key pools.

   Tip

   UltraLite APIs provide means of obtaining the proportion of numbers that have been used. The return value is a SHORT in the range 0-100 that represents the percent of values used thus far. For example, a value of 99 indicates that very few unused values remain and the database should be assigned a new identification number. The method of setting this identification number varies according to the programming interface you are using.