Skip to main content
Solved

How do I resolve an issue with unique key constraint violation when creating a snapshot after creating a custom table?

  • 14 July 2022
  • 7 replies
  • 725 views

I have created a custom table which was created using the following SQL:

IF Not Exists(select * from sys.tables where name = 'CustAIEverestData') 
CREATE TABLE [dbo].[CustAIEverestData](
    [CompanyID] [int] NOT NULL,
    [EverestDataID] [int] IDENTITY(1,1) NOT NULL,
    [CustCode] [char] (20) NOT NULL,
    [CustName] [char] (50),
    [EverestOrderDate] [datetime] NOT NULL,
    [EverestOrderNo] [char] (20) UNIQUE,
    [EverestSalesRep] [char] (10) NOT NULL,
    [EverestTerms] [char] (20) NOT NULL,
    [EverestTaxable] [decimal] (18,6) NOT NULL,
    [EverestTaxAmount] [decimal] (18,6) NOT NULL,
    [EverestInvoiceAmount] [decimal] (18,6) NOT NULL,
    [EverestFOB] [char] (20),
    [EverestDeliveryMethod] [char] (20),
    [EverestWeight] [decimal] (18,6) NOT NULL,
    [EverestTracking] [char] (50),
    [CreatedDateTime] [datetime] NOT NULL,
    [CreatedByID] [uniqueidentifier] NOT NULL,
    [CreatedByScreenID] [char](8) NOT NULL,
    [LastModifiedDateTime] [datetime] NOT NULL,
    [LastModifiedByID] [uniqueidentifier] NOT NULL,
    [LastModifiedByScreenID] [char](8) NOT NULL,
    [tstamp] [timestamp] NOT NULL,
    [NoteID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_CustAIEverestData] PRIMARY KEY CLUSTERED 
(
    [CompanyID] ASC,
    [EverestDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
) ON [primary]
GO

All seems fine until I tried to do a full snapshot on the tenant.  I am getting an error saying:

PX.Data.PXException: An error occurred while importing data into the 'CustAIEverestData' table. ---> System.Exception: Violation of UNIQUE KEY constraint 'UQ__CustAIEv__385E4D6441B3D86A'. Cannot insert duplicate key in object 'dbo.CustAIEverestData'. The duplicate key value is (4982760             ).

and the snapshot inevitably fails.

Is the problem in the way I defined the fields in the SQL?  The only unique field is EverestOrderNo so I suspect I should be using a different method of making sure duplicates cannot be imported into the same tenant as the same order no could exist in different tenants (and presumably needs to for the snapshot to work).  Does anyone know how I go about getting past this issue? How should I define the field so that duplicates cannot accidentally be imported into the same tenant but snapshots work correctly? Or am I misunderstanding the error and the cause?

 

Thanks,

 

Phil

7 replies

Leonardo Justiniano
Rank
Userlevel 6
Badge +5

Hi @ppowell 

Acumatica uses DAC definition of your custom table during snapshot restore. Make sure your identity column is marked as 

[PXIdentity(IsKey = true)]

I have faced this issue before where I needed to publish a customization before restoring a snapshot.

Leo
Dmitrii Naumov
Rank
Userlevel 7
Badge +5

You should not create tables with unique constraints not including CompanyID.

The companyID field is what's used to isolate snapshots from tenant's data, so it's required that it is a part of any unique index.

P
Rank
Userlevel 5
Badge +1

@Dmitrii Naumov Thanks, I suspected it was that.  Originally the OrderNo field was not set to be UNIQUE but later we decided to make it unique so stop duplicate records being imported in error. I’ve removed the OrderNo UNIQUE constraint for the table now which allowed me to complete the snapshot.  It now has the primary key of CompanyID and EverestDataID only.  I think to satisfy the requirements of what we need I should run the following SQL against it:

ALTER TABLE [dbo].[AICustEverestData] DROP CONSTRAINT [PK_CustAIEverestData];
ALTER TABLE [dbo].[AICustEverestData] ADD CONSTRAINT [PK_CustAIEverestData] PRIMARY KEY CLUSTERED(CompanyID ASC,EverestOrderNo ASC);

to change from using the EverestDataID and CompanyID to using the OrderNo and CompanyID as the key.

Does this sound right?

Thanks for your help and advice,

Phil

 

Dmitrii Naumov
Rank
Userlevel 7
Badge +5

Well, I guess you can do that, but I don't see why you need to remove the old PK. You can leave the old PK untouched and just add a new Unique index on the table with CompanyID and OrderNbr in the index.

P
Rank
Userlevel 5
Badge +1

@Dmitrii Naumov Thanks.  So I guess I don’t need to drop the old one and just do the following?

 

CREATE UNIQUE INDEX [UQ_OrderNoCompanyID]   
   ON [dbo].[CustAIEverestData] (CompanyID, EverestOrderNo);

Thanks,

 

Phil

Dmitrii Naumov
Rank
Userlevel 7
Badge +5

@ppowell yes, seems correct. 

Please also note that you can use PXCheckUnique attribute to force the system to check uniqueness of the field on the application level.

P
Rank
Userlevel 5
Badge +1

@Dmitrii Naumov I have successfully exported and reimported the snapshot now so all seems to be working correctly.

 

Thanks again for all your help,

 

Phil

Reply


Terms & ConditionsCookie settings