Unique Key Generation

Generating Unique Keys

To insert a new unique key into your record, you first need to generate a unique key value using the NEWID command.Or you can use one of the API calls and then insert it into your record using an insert statement.

The NEWID command follows this syntax

NEWID FOR table column
NEWID FOR table column n

Here are some examples of generating a new unique key using the NEWID command:

NEWID FOR MOVIE _rowid

The next command will generate 50 new unique keys and return them at once.

NEWID FOR MOVIE _rowid 50

This command is specific to OpenBase and is designed to be thread-safe and replication-safe. This means that the new key you get back will not be given out to another process that may ask for a new key at the same time. Since the NEWID command returns a value just like a select, it can be sent to the server through any interface which allows you to execute select statements and retrieve results.

In order for the NEWID command to work you need to make sure that the target column has a UNIQUE index. If the NEWID command is not incrementing your values (or is not unique) it is possible that you have not specified it as UNIQUE.

You also need to make absolutely sure that you specify the correct table and column where the value is to be inserted. One common mistake is generating a key using one column but inserting the value into another. This can cause conflicts since the keys are only unique to the values in a table and not across the entire database.

Once you retrieve a unique ID you can insert it with your record using a standard INSERT statement.

Creating Auto-Generating Key Columns

Every OpenBase SQL record has a column called _rowid which uniquely identifies it within a table. However, there may be times when you want to create your own auto-generating key value, similar to _rowid. To do this, we recommend using a derived column based on _rowid.

CREATE TABLE foo (MyKeyColumn longlong UNIQUE INDEX DERIVED _rowid)

This will create a derived column, MyKeyColumn, which will automatically be set to the value from the _rowid column.

Setting the SEED Value

A seed value is the value the key generation system uses to create the next key. Each time the values are incremented so that two keys are not given out twice. Most of the time you do not need to do anything for the SEED values to work the way you want. However, there may be some cases where you want to reset the SEED value on a table.

To reset the SEED value for an unique index, use the following command:

SET ID SEED n FOR table.column

Creating Auto-Generating Key Columns

Every OpenBase record has a column called _rowid which uniquely identifies it within a table. However, there are some cases where you may want to create your own auto-generating key value similar to _rowid. For this we recommend using a DERIVED column based on _rowid.

CREATE TABLE foo (MyKeyColumn longlong UNIQUE INDEX DERIVED _rowid)

This will create a derived column, MyKeyColumn, which will automatically be set to the value from the _rowid column.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License