Hi everyone,
I need to add a new mandatory column to database entity:
and I need to add an Index:
TicketId is FK.
When I try to publish, I encounter an error:
What is the best way to fix it?
Hi Lukasz,
The issue is that duplicate records already exist for the same key combination (TicketId + Number) in the entity. To successfully publish your changes, you must first be sure that no duplicates exist by identifying and removing them.
Ok, I know the source of the problem, but I don't know how to fix it.
During development, I can identify the records in the database that are causing this issue and update their values manually. Then the publish works, butut what will happen when I try to publish it to the TEST environment?
You need to do the same process of updating the values before you do the deployment to the TEST environment, otherwise the deployment will fail. Also take into account that the uniqueness applies to default values as well, so NullId Ticket + Number 0 cannot be duplicated.
Can it be done by timer with tirgger "When published"?
Yes, at this point it depends on your logic and what you intend to do with those duplicated records. However, the unique index needs to be created after the timer has run and the data has been cleaned up.
Adding to this scenarios, we cannot deploy both the timer (execute on publish) and new database change in 1 deployment session.
You need 2 deployment (as @Mihai Melencu mentioned, the timer must run before the new database change):
You can also use this component for and update data directly by SQL (its to replace the deployment batch for timer)
Hi,
the error tells you that the entity contains at least two records that do not meet the new constraint.
Lets say your entity contains 5 records and you add now a new mandatory attribute and set a unique index then 4 records out of the 5 do not meet that constraint and thus you get the error message.
In order to resolve this you need a two step approach. In the first step you
* create the new attribute
* run a data migration job using a timer action that is executed on publish. The action set a unique value for all existing records. (you can use a psql to do this in bulk if you don't care about the real ticket number by using e.g. the random function). Don't forget to add an If here to prevent the timer action to run in subsequent publish operations.
After that is done you can now set the unique constraint for the attribute.
Best
Stefan