Surrogate Database Keys

Section - TM Language Reference

Most advanced database systems use artificial key values for both primary and foreign keys. Such key values are only meaningful inside the database instance and invariably each database system supplies some mechanism for producing such keys.

In general database systems use three types of mechanism to provide surrogate artificial key values:

• A dedicated sequence
• A dedicated table
• Something else

SML provides special facilities to cover all options. It also supplies a simple default mechanism for writing into database that will only be used by the Transformation system itself.

The only difference between the systems lies in the reserved words used. The default system uses $UniqueKey.
The more complex system may be invoked using the UniqueKey function:

Interactions between Primary and Secondary Keys

TM Design Tool supports many different styles of interaction with primary and foreign keys. Here are our general recommendations - your particular circumstances may render these recommendations inappropriate.

When reading from a database:

• do not read the values of primary or foreign keys. Allow TM to navigate the implied relationships directly. It is also unlikely that the actual instance values in primary surrogate keys should be copied to the target.

When writing to a database there are two strategies:

• Ask TM Design Tool to automatically supply primary key values (Edit | Preferences | SML Generation | Supply Pkeys Automatically
• Explicitly set all primary key values.

Irrespective of which of these two approaches is used DO NOT WRITE directly to foreign key values. If the database contains any foreign key constraints then such an approach will almost always fail. Even if the database does not contain foreign key constraints writing to foreign key values directly at best will merely duplicate TM Design Tool's internal logic for setting relationships (and public impact performance) while at worst subtle conflicts between TM Design Tool's logic and the explicit SML code in your transform will cause fatal errors at run-time.

SML provides a special keyword, $UniqueKey and a special function, UniqueKey for providing artificial values.  Be aware that such values must be assigned directly to attributes in the database. It is quite incorrect to use UniqueKey values for any other purpose - for example to assign a value to local variable. This is because the RDBMS target adapter does not use $UniqueKey to actually FIND a row in the database - only to re-find the same row.