SQL Virtual Keys

The use of virtual keys enhances the CA IDMS SQL capability to access network-defined databases.
idmscu19
The use of virtual keys enhances the CA IDMS SQL capability to access network-defined databases.
Key Benefits
Virtual keys enable modern SQL programmers and development tools to access CA IDMS without knowing CA IDMS or any CA IDMS extensions or special syntax. Virtual keys allow you to have a referential constraint which can be used to access and manipulate a network database through SQL.
Using virtual keys provides the following added benefits:
  • Does not require database changes
  • Does not require table procedures
  • Does not require changes to network definitions
  • Allows flexible navigation, similar to network DML
  • Provides easy, non-disruptive adoption
Referential Set Relationship Using Virtual Keys
ROWID as Virtual Primary Key
Each record has an implicitly defined primary key, accessible as the single pseudo column ROWID. The value of ROWID is based on the dbkey of the record. ROWID is not a persistent primary key, but within a transaction it can be used as a primary key.
For more information about ROWID see ROWID Pseudo-column.
Virtual Foreign Key
Each record gets a set of implicitly defined virtual foreign keys. There is one virtual foreign key for each set in which the record is a member. This virtual foreign key consists of a single pseudo column with name FKEY_setname, where setname is the transformed name of the set. The name transformation consists of replacing minus characters (‘-‘) with underscore characters (‘_’) and other replacements as required.
In a multi-member set each of the member records will get an identically named virtual foreign key. This name collision is handled in the standard SQL way by qualifying the pseudo column with the table name or an alias of the table.
FKEY_setname attributes:
  • Is a pseudo column, similar to ROWID, but is only defined for a table or a view that represents a network record in which the network record is a member of a set.
  • The data type is ROWID which is 8 bytes in length.
  • Contains a 4 byte dbkey followed by 4 bytes of page info or is the NULL value. This 8 byte ROWID value uniquely identifies a row of the referenced table.
  • Is always nullable even for sets that are MA, so that the API does not depend on set properties.
  • Participates as a foreign key in the referential relationship with the ROWID pseudo-column as the primary key.