"ERROR: During insert: Violation of PRIMARY KEY constraint" and "Cannot insert duplicate key in object" occur in the SASCustIntelCore6.x.log file


Error messages similar to the following occur in the SASCustIntelCore6.x.log file, which is located at [Config]\Lev1\Web\Logs\SASServer6_1\SASCustIntelCore6.6.log:

ERROR: During insert: Violation of PRIMARY KEY constraint

and

Cannot insert duplicate key in object

These errors indicate that you very likely have duplicate entries for the Surrogate Key (SK) number in the sequence table located at [Config]/Lev1/Applications/SASCustomerIntelligence/CustomerIntelligenceCommon
/Data/MAMisc. (For example, SkCellPackage or SkCommunication.)

Here is an example of a sequence table with duplicate entries:

Sequence table with duplicate SKCellPackage entries.

The sequence table is used to get the next available SK numbers for insertion into the Common Data Model (CDM) tables. For example, CELL_PACKAGE_SK, COMMUNICATION_SK, CAMPAIGN_SK, and so on. As a result, the table should not contain any duplicate entries.

Note that the sequence table needs to have Read and Write permissions, so duplicate entries mostly occur when the table was not available for writing (sometimes in the past).

Workaround

To circumvent this issue, you need to delete the duplicate entry—preferably entries with the lower value for the SK number—and increase the number of the higher entry.

  1. Verify the highest available number from your CDM tables. For example, open SAS® Enterprise Guide® and run either of the following. Note: Use the libref to your CDM tables.

    PROC SQL;
    select max(communication_sk) from cdm.ci_communication;
    quit;

    PROC SQL;
    select max(cell_package_sk) from cdm.ci_cell_package;
    quit;

  2. Make a back-up copy of the existing sequence table located in [Config]/Lev1/Applications/SASCustomerIntelligence/CustomerIntelligenceCommon
    /Data/MAMisc     and put the table in a different location. Be sure to note the current permissions on the table.

    Note: Complete the next steps while the SAS environment is down and nobody is able to execute any campaigns (neither scheduled nor executed) inside SAS® Customer Intelligence Studio. A good time to complete these steps is during a maintenance window when the system is already scheduled to be down.

  3. Update the sequence table from [Config]/Lev1/Applications/SASCustomerIntelligence/CustomerIntelligenceCommon
    /Data/MAMisc    . 

    libname abc

    'C:\SAS\Config\Lev1\Applications\SASCustomerIntelligence\
    CustomerIntelligenceCommon\Data\MAMisc';

    proc sql;

    update abc.sequence

         set number = 667783   → add approximately 20 more to the highest value that you saw in step one

    where sequence = "SkCellPackage";

    quit;


    To verify that the expected value is displayed, run the following code:

    proc contents data=abc.sequence; run;

    proc print data=abc.sequence; run;

  4. Ensure that the new sequence.sas7bdat has Read and Write permissions.
  5. Add the symbolgen to the maspinit.sas file, which is located as follows. Note that this step will provide you with more details in case there are ever issues with the sequence table now or in the future.

    UNIX: <SASHome/SASFoundation/9.4/sasautos/maspinit.sas 
    Windows: SASHome>\SASFoundation\9.4\cicsvr\sasmacro 

    %macro maspinit(xmlstream=);

        %global maDebug MAError MAMsg MANodeName MATableName MAColumnName MATables;

        %if &maDebug eq Y

        %then %do;

            option mprint source source2 stimer notes symbolgen;

        %end;

  6. Close SAS Enterprise Guide and the sequence table.
  7. Restart the environment.