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
  • 526 views

Userlevel 5
Badge +1

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

icon

Best answer by Dmitrii Naumov 14 July 2022, 13:58

View original

7 replies

Userlevel 6
Badge +4

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.

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.

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

 

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.

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

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.

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


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved