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?


Forum|alt.badge.img+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

Best answer by Dmitrii Naumov

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.

View original
Did this topic help you find an answer to your question?

7 replies

Leonardo Justiniano
Jr Varsity II
Forum|alt.badge.img+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.


Dmitrii Naumov
Acumatica Moderator
Forum|alt.badge.img+6
  • Acumatica Moderator
  • 595 replies
  • Answer
  • July 14, 2022

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.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro I
  • 133 replies
  • July 14, 2022

@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
Acumatica Moderator
Forum|alt.badge.img+6
  • Acumatica Moderator
  • 595 replies
  • July 14, 2022

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.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro I
  • 133 replies
  • July 14, 2022

@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
Acumatica Moderator
Forum|alt.badge.img+6
  • Acumatica Moderator
  • 595 replies
  • July 14, 2022

@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.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro I
  • 133 replies
  • July 18, 2022

@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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings