Creating a new CI Class results in an error "AHD04116:A duplicate record was encountered. Insert or Update failed"

Document ID : KB000050070
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Service Desk Manager is trying to insert an ID value into ca_resource_class table that already exists. Since this id already exists, it generated the error saying id already exists.

You need to update the intkeys_intkey value to a greater value than max(id) value in the ca_reg_intkeys table for the intkeys_table column 'ca_resource_class'

Solution:

There is table called ca_reg_intkeys in the MDB. This table contains information on generating integer keys for different CI related tables. If you run a SQL Query select * from ca_reg_intkeys

# The first column "intkeys_table" gives you the table name. In this case it is ca_resource_class.
# The "intkeys_column" is the column on which the id will be generated. In this case it is the id column of the ca_resource_class table.
# The "intkeys_intkey" column value is the one from where the value is being referenced when the CI Class IDS are generated.

Below is an example

select * from ca_reg_intkeys where intkeys_table='ca_resource_class'
The intkeys_intkey initially was set to 1000078.

First identify the highest value id in the table.

If using MS SQL:

 use mdb;         SELECT TOP 1 id FROM ca_resource_class 

If using Oracle:

 SQL> select MAX(id) 2 from mdbadmin.ca_resource_class;

Now if you want to start a fresh set of ids starting from 1000101 for the ca_resource_class. So go ahead and updated the ca_reg_intkeys table as shown below:

update ca_reg_intkeys set intkeys_intkey=1000101 where intkeys_table='ca_resource_class'.

Now by running a SELECT Query again on this table, we can see the intkey value gets updated. When you start creating new CLASSES from the interface, the number will start from 1000101 and so on...

It is also important to note that after you update intkeys_intkey value, and after that create a new class for the first time, the intkeys_intkey value will be incremented by 50. This is by design. So you will see after creating a new class from the Web Interface, the intkeys_intkey value in the ca_reg_intkeys table will change from 1000101 To 1000151.