|
You can specify that a domain must generate unique values. The result is that, in each column based on the domain, for each new record, the application will automatically generate a numerical value that is unique for that record in that column.
The default for the Generate Unique Values domain attribute is No.
If you set Generate Unique Values = Database:
•On Oracle or SQL Server, unique values are generated by a database sequence. •On other platforms (ODBC, JDBC, Derby), unique values are generated by a USoft sequence.
Generated unique values are meaningless key values that act as a handle for recognising each individual record. Generated unique values are thus appropriate for primary key columns.
Unique values in primary keys are system-generated, not user-defined. Users are not allowed to enter or change generated values in on-screen fields: these fields are query-only.
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.
|
The Generate Unique Value domain attribute allow 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.
|
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. |
|
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. |
| USoft simulates a database sequence by keeping track of unique numbers in the TABLE_SEQUENCE_NUMBERS system table. |
|
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, set 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 this domain, 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. |
NOTE: You can have a component generate the unique values for the primary key of a database table, but also for the primary key of a component table.
|
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
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.
|
See also
Domains
|
