Working with primary keys

In versions of Service Manager earlier than 9.32, Service Manager did not support primary keys or Not Null constraints in the RDBMS tables. While the Service Manager logical representations did include support for unique keys with a Not Null constraint, this support was managed entirely within Service Manager and not in the RDBMS. Because of this lack of functionality, SQL queries generated by Service Manager added an Is Null condition in the JOIN/WHERE sub-clause as shown in the following example:

SELECT m1."RELATIONSHIP_NAME", m1."LOGICAL_NAME"
FROM CIRELATIONSHIPM1 m1 LEFT OUTER JOIN CIRELATIONSHIPA1 a1 
ON
  ( ( (m1."LOGICAL_NAME" = a1."LOGICAL_NAME")
 OR (m1."LOGICAL_NAME" IS NULL AND a1."LOGICAL_NAME" IS NULL) ) 
AND
    ( (m1."RELATIONSHIP_NAME" = a1."RELATIONSHIP_NAME") 
OR (m1."RELATIONSHIP_NAME" IS NULL AND a1."RELATIONSHIP_NAME" IS NULL) ) )

WHERE …

This mechanism resulted in poor performance when you consider the speed advantages of leveraging the inherent integrity of the database.

In Service Manager 9.32 or later versions of Service Manager, primary keys and Not Null constraints on the unique keys are now supported.

Usage

From command line

The sm –system_addconstraint command line is used to add either NOT NULL or primary key constraint to the existing files in SM.

There are three levels of operations you can perform by using this command:

1. Add the NOT NULL constraint only

   While Service Manager already has a No Nulls key type, this key type is in fact mapped to an normal index in RDBMS. The NOT NULL constraint added by system_addconstraint command adds a true constraint in the RDBMS (such as the NOT NULL constraint in and Oracle database.

   The NOT NULL constraint is invisible to end users; End users cannot add, modify or delete it from anywhere in the Service Manager client.

2. Modify the first unique key to primary key

   By using this command line, the Service Manager server can convert the first unique key of the Service Manager files to true primary keys in RDBMS. However, if the data in the table is not qualified for the primary key constraint, the conversion from unique key to primary key will fail.

3. Modify the first unique key to primary key more aggressively

   If you can afford the risk of lost data in the RDBMS, you can use this level of operation. At this level, the system_addconstraint converts the first unique key to a primary key more aggressively. That is, a temporary table will be created, all the qualified data will be moved to this new table, and the old table is dropped, and the temporary table is renamed to the original one.

   This process is referred to as a full table copy; unqualified data is lost, and qualified data is left.

From SM client

From the Service Manager client, users can add, modify or remove the primary key type just like any other key type.

Considerations

The following behavior is subjected to the primary key feature:

• When there is data in a table, you cannot modify the name of the primary key column.
• If there is a RECORD_KEY column and one unique key column in a table, the RECORD_KEY column is removed, and the first unique key is converted to a primary key when you run the sm –system_addconstraint command.
• Service Manager automatically creates a hidden RECORD_KEY field when there is no primary key or unique key in the Database Dictionary file. It is recommended that you explicitly define a primary key when creating a new dbdict record or convert the first unique key to a primary key if you think it necessary for better performance or other reasons.
• Once the database is converted to primary key mode by using the system_addconstraint parameter, the first unique key in the Database Dictionary is changed, and a hidden RECORD_KEY field may or may not have been created accordingly. This behavior could potentially impact any integration you may have implemented in Service Manager. For example, the legacy SCENTER integration and ODBC driver may rely on the primary key field directly. If such integration does not work as expected after you apply the primary keys, you should review and modify the integration accordingly.
• The type of a newly-created RECORD_KEY field is now VARCHAR2 or VARCHAR in Service Manager. However, any existing RECORD_KEY fields remain as INTEGER.

Retrieve primary key values

Retrieve primary key mode

Modify keys: Add Not Null constraints to the first unique key in a table

Modify keys: Convert the first unique key to primary key