Administer > Database Administration > Data persistence > Keys and indexes in HPE Service Manager > Modifying a Key > Modify keys: Add Not Null constraints to unique keys

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

To add a Not Null constraint on a key by using the command line, enter the following command from the operating system command prompt:

Note

  • Make sure that Service Manager is offline before you execute this command. Otherwise, the "Not Null" constraint may be lost after you modify the dbdict. In this case, you would need run the command again.

  • Due to differences in how databases handle "Not Null" constraints, you should note the following behaviors:

    For Oracle and DB2 databases: a "Not Null" constraint is added to the unique index.

    For SQL Server database, a "Not Null" attribute is added to each field of the unique index.

sm -system_addconstraint:<target>:0

In this example, <target> represents the tables on which you wish to add the constraint, and 0 represents that you wish to add a Not Null constraint on the first unique key of the <target>.

After you run this script, you will see information that resembles the following in the sm.log file.

 11508( 12940) 12/11/2012 11:24:59  RTE I Adding NOT NULL constraint for table 'Alertlog'.
 11508( 12940) 12/11/2012 11:24:59  RTE I Adding NOT NULL constraint for mapped table 'ALERTLOGM1'.
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for field 'ALERT_ID' successfully!
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for mapped table successfully, updated to DBDICT!
 11508( 12940) 12/11/2012 11:24:59  RTE I Adding NOT NULL constraint for table 'Approval'.
 11508( 12940) 12/11/2012 11:24:59  RTE I Adding NOT NULL constraint for mapped table 'APPROVALM1'.
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for field 'UNIQUE_KEY' successfully!
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for field 'FILE_NAME' successfully!
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for field 'NAME' successfully!
 11508( 12940) 12/11/2012 11:24:59  RTE E Error: SQL code=2293 message=ORA-02293: cannot validate (SM930GA.APPROVALM18C1CC5FB) - check constraint violated
 11508( 12940) 12/11/2012 11:24:59  RTE E API=OCIStmtExecute [in sqociExecOne], Statement=ALTER TABLE APPROVALM1 ADD CONSTRAINT APPROVALM18C1CC5FB CHECK( "COMPONENT" IS NOT NULL )
 11508( 12940) 12/11/2012 11:24:59  RTE W Adding NOT NULL constraint for field 'COMPONENT' failed, please change/remove the NULL values in records!
 11508( 12940) 12/11/2012 11:24:59  RTE I Adding NOT NULL constraint for mapped table 'APPROVALA1'.
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for field 'UNIQUE_KEY' successfully!
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for field 'FILE_NAME' successfully!
 11508( 12940) 12/11/2012 11:24:59  RTE I Add NOT NULL constraint for field 'NAME' successfully!
 11508( 12940) 12/11/2012 11:24:59  RTE E Error: SQL code=2293 message=ORA-02293: cannot validate (SM930GA.APPROVALA153D2D5D4) - check constraint violated
 11508( 12940) 12/11/2012 11:24:59  RTE E API=OCIStmtExecute [in sqociExecOne], Statement=ALTER TABLE APPROVALA1 ADD CONSTRAINT APPROVALA153D2D5D4 CHECK( "COMPONENT" IS NOT NULL )
 11508( 12940) 12/11/2012 11:24:59  RTE W Adding NOT NULL constraint for field 'COMPONENT' failed, please change/remove the NULL values in records!

Lines in blue mean Service Manager starts to add a NOT NULL constraint for a logical table in SM.

Lines in dark yellow mean Service Manager starts to add a Not Null constraint for a physical table in SM.

Lines in red mean Service Manager cannot add a Not Null constraint to that field, because that field has NULL values in records. Therefore, you should remove these NULL values manually.

Send Help Center feedback