On Oracle and SQL Server, you can set 7 additional attributes to control exactly how a database sequence for a column based on the domain should behave:

Generated unique values are meaningless key values that act as a handle for recognising each individual record. This is why you can have generated unique values only for primary key columns.

In a USoft Relationship, you must use the same domain for the primary key column(s) and for the foreign key column(s) that refer to it (them). If the primary key has generated unique values, these will be unique across the parent table. However, corresponding foreign key values in the child table will typically NOT be unique across the child table.

If a column based on a domain with generated unique values is PART of a primary key, in other words, if it is one of the columns of a multi-column primary key, then unique values will be generated accordingly. In this case, it is the multi-column value combination that will be unique, not (or not typically) the individual column values.

Generated unique values in primary keys are system-generated, not user-defined. What you normally want is that interactive users can query these values, but are not allowed to enter or change these values.

In USoft default web pages, screen fields corresponding to these values are query-only. Interactive users cannot insert or update values in these fields.

If you import data using XML.Import or related techniques, you can supply external unique values. These values will be correctly imported and not overwritten by sequence-generated values, on condition that they lead to a situation where each record has a unique value (otherwise, it's an error).

The Generate Unique Value domain attribute allows unique values to be generated, but uniqueness of values in a column is ultimately checked not by this attribute but by unique indexes.

USoft Definer automatically creates a unique index for each primary key that you define. In addition, you can manually add additional indexes for which you set Unique = Yes.

    Unique values generated by an Oracle or Sql Server sequence

On Oracle or SQL Server, for columns based on a given domain, to have unique values generated by a database sequence:

1.    Open the Domains window for the domain for which you want unique value generation.
2.    Set Generate Unique Values = Database.

By default, sequence numbers start with 1, and increment by 1. On Oracle or SQL Server, however, you may change these settings:

3.    In the "Sequence Definitions (Oracle & Sql Server only)" box, specify the increment, the starting value, the maximum and minimum values, and the cycle, cache, and order values.
4.    Save work.

    Unique values generated by a USoft sequence

On ODBC, JDBC or Derby, for columns based on a given domain, to have unique values generated by a USoft sequence:

1.    Open the Domains window for the domain for which you want unique value generation.
2.    Set Generate Unique Values = Database.
3.    Save work.

USoft simulates a database sequence by keeping track of unique numbers in the TABLE_SEQUENCE_NUMBERS system table.

You can have unique values by having USoft generate a random or GUID value of the following type:

dd934d9119b445d192d30f0c26afb5fc

To have generated GUIDs:

1. In the Domains window, create a Domain D with Generate Unique Values = GUID.

Now, each time you base a column on domain D, USoft will generate a GUID value for that column in each new runtime record. A typical application of GUIDs is for primary key columns.

 You can have unique values generated by an RDMI component that you write yourself and declare in USoft Definer. The advantage is that you can determine all the details of unique value generation yourself. The disadvantage is that this option requires more work, especially to maintain the solution in the long run.

For each primary key column based on the domain, your component needs to have a separate method called "<Table Name>GETSEQUENCENUMBER", for example: "PERSONGETSEQUENCENUMBER". The corresponding implementation, eg., "PersonGetSequenceNumber()", must executed the desired behavior.

To have unique values generated by an RDMI component:

1.    Make a first implementation of the component and declare the component in the RDMI Components window or tab.
2.    In the Domains window, create a Domain D with Generate Unique Values = Component.
3.    In the dropdown box immediately to the right of the Generate Unique Values field, using the small lookup button to its right, identify the component that you want to use.
Now, each time you base a primary key column on domain D, a <TableName>GETSEQUENCENUMBER method is added to the component definition. Parameters are added for the other primary key columns.
4.    (Re-)write the <TableName>GetSequenceNumber() method in the component code, and let this method provide the sequence value. This method is invoked when inserting a record.

If a table contains a key column for which the domain has the "Generate Unique Values = Database" setting, each time a new row is INSERTed into this table, the Rules Engine automatically generates a new unique value for that key column.

If you are in client/server mode, you can see this new value directly after INSERTing the row. This behaviour is referred to as "update-notify".

But if you are using the Remote Rules Service API or the Rules Service, the underlying protocol (ODBC or JDBC) which allows you to communicate with the Rules Engine does not provide an "update-notify" mechanism, so that you cannot see the newly generated key value. This makes it difficult to retrieve the row that you have just inserted.

To get around this problem, USoft provides a simple mechanism. The Rules Engine keeps the last unique value it has generated, and this value can be retrieved by the statement:

INVOKE RulesEngine.getLastGeneratedUniqueValue

You can also use function call syntax:

SELECT RulesEngine.getLastGeneratedUniqueValue()

The Rules Engine only retains the generated value of the LAST USER-DEFINED record. It does not heed INSERTs performed by non-interactive constraint action.

If a table contains a key column for which the domain has the "Generate Unique Values = GUID" setting, each time a new row is INSERTed into this table, the Rules Engine automatically generates a GUID for that key column.

The Rules Engine keeps the last GUID value it has generated. This value can be retrieved by the statement:

INVOKE RulesEngine.getLastGeneratedGUID

You can also use function call syntax:

SELECT RulesEngine.getLastGeneratedGUID()

The Rules Engine only retains the generated value of the LAST USER-DEFINED record. It does not heed INSERTs performed by non-interactive constraint action.