pdm_load errors out with a valid load file, and SQL error "FOREIGN KEY SAME TABLE constraint" in stdlogs.

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

Problem:

When using pdm_load, an error may present, despite the same load file working on other environments.

Steps to Reproduce:

  1. Obtain an extract of a given contact record in the first Service Desk install.
    Example command:  pdm_extract –f “Select * from ca_contact where last_name = ‘Smith’” > load1.dat

  2. Copy load1.dat to a second Service Desk Server (same version and patch level)

  3. Run the following command to load the file:  pdm_load –f “load1.dat”

Error will appear for the load.  The following message may be seen in the stdlogs:

08/12 15:01:54.05 SERVER1 sql_agent 10040 ERROR sqlclass.c 1010 SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=547 SQL State=23000] The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "ininjoin01". The conflict occurred in database "mdb", table "dbo.ca_contact", column 'contact_uuid'.


Environment: 

CA Service Desk 12.x and 14.x


Cause
:

The logs reference an error with FOREIGN KEY SAME TABLE constraint "ininjoin01".  A Foreign Key is a construct in database design in which the field or fields named in the foreign key is a reference to a record in another table, or in this case, the same table.  What happens in a foreign key relationship is that the record which is referenced in the given field defined in a given entry in a load file must exist in the database table BEFORE the entry can be loaded.  In the above case, "ininjoin01" is a defined foreign key that references the supervisor_contact_uuid field.

In the first SDM installation, there is a user named “Smith” and "Smith" will have a supervisor defined; we’ll call that user “Jones”.  As a result, the above load file will contain a contact entry for "Smith", whose supervisor_contact_uuid field on the "Smith" contact will contain a UUID value that belongs to user “Jones”. 

In the second SDM installation, where the load file is being brought in, the load will fail because the contact "Jones" does not exist in the second SDM installation, and must be defined EXACTLY as it is in the first SDM installation (right down to Jones’s UUID value).  Once "Jones" has been defined exactly in the second SDM installation as it is in the first SDM installation (including UUID value entry for "Jones"), the load file containing the contact entry for "Smith" can be brought into the second SDM installation from the first SDM installation.


Workaround:

Corrective actions to be considered would be to either:

- Blank out the supervisor_contact_uuid value in the given load file, then attempt to load the file again.

- Locate the given contact who is assigned as the given contact’s supervisor and bring that user into the affected install before loading the file.  Keep in mind that the given contact may also have a supervisor or other similar foreign keys defined whose records will also need to be brought in beforehand.

- Another option to consider is to wipe the contents of the second SDM install's MDB database, and copy in the entire MDB database from the first install.  This is feasible if the original content of the second SDM install is of no value whatsoever.

 

Additional Information:  

Please note that there is an inherent risk when transferring individual data components across SDM environments. There is the potential of incomplete data being brought in between environments, and constraints in play, such as foreign keys, which will check for such inconsistencies that may prevent the data load from taking place.

It is inadvisable and unsupported to try and disable such constraints directly at the database level as this may lead to adverse effects, including the potential of rendering the installation unusable.

See also:

TEC617303
Is it possible to extract and load CA Service Desk (CA SDM) Group information from one environment to another?