Hello Neha,

Update<your entity name here> action will perform an update in an existing record.
The error is not related to an "INSERT". It is realted to a field (Year?) that has an INDEX assocaited to it, and the Unique property set to TRUE.

This mean that no two records with the same value for this atribute can exist in the entity.

It seems that you are not providing this value, as the message says it is 0, and you already have one row in the entity where the year is set to 0.

Check your logic to be sure you are providing a Year value, or if it should be allowed to have repeated values, to remove the Unique of the index.

Cheers.

Can u please share correct snap "Year and RecordDate"

Hello Neha,

The error is exactly the same as before, but now for column Year.

You have an UNIQUE index for year column, so database will not allow you to insert a new record with a value for the year that already exists in the table.

As before, or you remove the unique from the index to allow multiple records for the same year, or you don't try to insert a record with a year that is already in table. What suits best your use case.

Cheers

Correct..

But for confirmation can u plz share the right snap with data...Thanks

Neha, 

You need to understand how INDEXES work.

First stop: https://success.outsystems.com/Documentation/10/Reference/Data/Database_Reference/Database_Indexes

Second stop: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Data_Modeling/Create_an_Entity_Index

Basically, an index helps database be more efficient (and fast) during search. In OutSystems, we also use them to define that an attribute, or a group of attributes can not appear repeated in the entity.

Example. Take the beloow entity.

MyEntity
id - Name - Age
1  - Joao - 21
2  - Maria - 22

a) Index UNIQUE for Column Name

If I try to insert (Pedro, 21) -> Ok
If I try to insert (Maria, 30) -> FAIL, because there is already a record (id 2) in the database with name Maria

b) Index UNIQUE for columns Name AND Age (in the same index)

if I try to insert (Joao, 25) -> Ok, because there is NO record with both values already in the table
If I try to insert (Pedro, 21) -> Ok, because there is NO record with both values already in the table
If I try to insert (Joao, 21) -> Fail, because there is  a record (id 1) with both values.

The error message you are receiving is this:

Cannot insert duplicate key row in object 'dbo.OSUSR_CF9_FIXEDCOST1' with unique index 'OSIDX_OSUSR_CF9_FIXEDCOST1_4YEAR_10RECORDDATE'. The duplicate key value is (2018, ).

So, you have an INDEX in your entoty, that is marked as UNIQUE (will not allow records with all the values in the attributes of the index to appear repeated), and it is saying to you that you are trying to insert a record with values for its attributes (the attributes in the index) that already exist in the entity.

So, the solution is:

1. If repeated values for this "group" of fields should be allowed, remove the UNIQUE of the index.
2. If repeated values for this "group" of fields are not allowed, fix your logic to guarantee you do not try to insert repeated values.

Cheers.

If u have unique index on those column ...even u can not update the same values.

Neha,

Doesn't matter.

If you violate the unique index when changing an existing record, it will trigger an exception.

Using the examples given above, but now with an UPDATE instead of an INSERT.

MyEntity
id - Name - Age
1  - Joao - 21
2  - Maria - 22

a) Index UNIQUE for Column Name

If I try to change (Joao, 21)  to (Maria, 21) -> FAIL, because there is already a record (id 2) in the database with name Maria

b) Index UNIQUE for columns Name AND Age (in the same index)

if I try to change (Joao, 21) to (Maria, 22)  -> Fail, because there is  a record (id 2) with both values.

Cheers.

Hi Neha, 

As you can seee here, no errors happen from just update the record seeting the IsLocked to True or False.

I used the standard logic (you can check in the attachement).

So, my entity has the same index you say you have, and no error rises when updating a record.

Getting the record with an GetForUpdate and updating it also does not raises any exception (as expected).
Could you paste here your indexes for this entity, as well as the whole code that is doing the update?

Cheers.

Hello all, I know this thread is somewhat old, but I am facing a similar question.

I have an entity with one field with Unique index

Lets say entity - Product and ProductCode has unique index.

Lets say I have a Product with ProductCode value equal to "123" (recordA). I fetch that Product, use the record retrieved from the aggregate and set value of ProductCode = "123" (the exact same value that is already there)

Afterwards I use recordA on a CreateOrUpdate action. Will it still fail?

I am using the same value that already exists on the database but updating it on the record where it is currently existing. Will I still get the duplicate error? Or only if I try to create or update another record (recordB) with ProductCode = "123" will I get the duplicate error?

Thank you all, and sorry if this is already answered above, but reading this thread it seems this particular use case is not totally clarified here.