Expand my Community achievements bar.

SQL Error changing from custom primary key to Auto

Avatar

Level 3

When changing a custom resource from having a custom primary key to an auto key we are getting this error:

 

PGS-220000 PostgreSQL error: ERROR: could not create unique index "customfield_id" DETAIL: Key (****)=(0) is duplicated.

 

I've not yet found a solution to resolving this so that the custom primary key is removed, does anybody have any solutions?

5 Replies

Avatar

Employee Advisor

Hi Michael,

 

I would suggest engaging Campaign Support. This error can be caused by numerous conditions when making changes or publishing the custom resource; however, from what I can see on my end, this error has not been reported previously using your reproduction steps and will require further investigation.

 

Thanks,

Craig

Avatar

Level 3

Thanks @Craig_Thonis I'm in touch with Campaign Support as well. They're looking into it, but sometimes I have found it quicker to get solutions here

Avatar

Administrator

Hi @michaelwilding,

Were you able to get a resolution from the Campaign Support? Kindly share the solution here with the Community as well, so that users facing similar issues can also gain from your solution. 

Best,

Sukrity 



Sukrity Wadhwa

Avatar

Level 3

HI @Sukrity_Wadhwa I did, it took about 3 months but finally got there! The bottom line is that you cannot remove a custom key from a custom resource if there is data in the table. The only way to do it is to delete all the data in the table, adjust the custom resource and then re-import the data. The reason we needed to remove the custom key is because if you add a custom key to a custom resource, every profile needs to contain that custom key for functionality such as pre-filling forms etc. to work, if it is missing it doesn't default back to the standard key to find the information needed.

So adding custom keys needs to be considered very carefully. For us, we'll probably always use the automatic custom key on a custom resource so there is a key field, but not make our own again, simply link them, unless you can see a reason not to have the custom key at all?

We are in the process of working out how we are going to manipulate the data to remove it all and put it back in, so I've only tested the changes on staging so far, where they worked, but will confirm if they work on the production environment when we have done it.

Avatar

Administrator

Thanks @michaelwilding for sharing your learnings and insights with the Community! We will await your update once you are able to reproduce the results in prod. 



Sukrity Wadhwa