Creating an IDKEY with a chosen name

What do you do if you want to have the ID field have a meaningful name for your application?

Sometimes it comes to pass that when you're making a new table that you want to have the unique row identifier (a.k.a. IDKEY) to be a field that has a name that is meaningful for your data. Moreover, sometimes you want to set this value directly. Caché fully supports this functionality and it works Suppose you have a class Test.Kyle. The data will be stored like so:

^Test.Kyle(IDKEY)=$LB("",Field1,Field2,...,Fieldn)

Any other field can be unique, but the IDKEY is the one that is the subscript for all the data. There are two ways to create a named IDKEY.

Property KyleID as %Integer [Identity];
Property KyleID as %Integer;

Index IDKEYIndex on KyleID [IDKEY];

Number 1 works like the default IDKEY. It is not setable, and is automatically generated using $INCREMENT.

Number 2, on the other hand, is a property that you have to set manually (though you could give it an initial value). Moreover, the field being part of the IDKEY means it is inherantly required, though that keyword is never used.

Thomas Carroll · Feb 2, 2016

Thanks Kyle!

Just for clarity, here's a little more context regarding the Identity keyword and the IdKey keyword. Specifically, regarding what other effects these might have on your table/class design.

Identity

Like the Highlander, there can only be one property marked [Identity], otherwise you will get a compile error. This is pretty intuitive, but the reason for this is that the property is being mapped to an "identity column" (really, SQL IDENTITY) in the SQL table.
Identity properties MUST be of type %Integer.

IdKey

This keyword specifies that the property(ies) that we're using to build this index will be combined to form the "Object Identity" value for the instance of the object in question.
- The "Object Identity" value is what use to locate and uniquely identify persistent objects.
- As a result, if there is an IdKey index built using certain properties, you CANNOT modify the values of those properties after you have saved the object (or inserted the row).
"IdKey" indexes are "Unique". That is, it is redundant (but allowed) to have an index tagged as both Unique and IdKey
It is not recommended to have "||" (sequential double pipes) in the properties that are used to build the IdKey index. This will result in unpredictable behavior.
- This is an artifact of how Caché SQL engine works.
- The exception, of course, is if that property is a valid reference to a persistent class.

0 0

David Van De Griek · Feb 3, 2016

There are a couple of other options for this topic.

If you are simply wanting the ability to define the name of the "ID" field yourself, you can use the class keyword SqlRowidName. For example:

SqlRowIdName = PersonId

For an Identity property, there is a way to allow a process to explicitly specify values for the identity property/field. This is typically useful when first populating a table/object from an external source and you want to retain the identity's values.

The call is: set oldValue = $SYSTEM.SQL.SetIdentityInsert(1,.sc)

For more information on the $SYSTEM.SQL.SetIdentityInsert call see class documentation for the %SYSTEM.SQL class.

Here is an example:

0 0