SQL Error changing from custom primary key to Auto

[michaelwilding]

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?

[Craig_Thonis]

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

[michaelwilding]

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

[Sukrity_Wadhwa]

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 

[michaelwilding]

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.

[Sukrity_Wadhwa]

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.