Skip to main content
K
knakaya6-Contributor
6-Contributor
January 11, 2019
Solved

Cannot insert two records has two primary key.

  • January 11, 2019
  • 1 reply
  • 2096 views

Hello All,

I created datatable contains 2 primary key(col1, col2).
I insert 2 records("a","bc")("ab", "c") into the table,
but auto generated key field conflicts and updated(not insert).

How can I insert these two records in that datatable?

1.png2.png

 

 

Best answer by CarlesColl

You should create a "fake" primary key on inserting, like an autoincrement field ( or use a GUID value ), or also on your case ( if col1 / col2 length has maximum length, say 5 ) you can create the fake primery key as:

 

var fakePrimaryKeyColumn = (("00000"+col1).slice(-5)+("00000"+col2).slice(-5));

 

 

1 reply

T
tkawasaki5-Regular Member
5-Regular Member
January 11, 2019

Hi,

 

I think it's not possible to do that.

ThingWorx stores datatable records into a table called data_table. The table has a column called entity_key and primary key values are concatenated and stored in this column.

Before inserting a new record into the table, ThingWorx checks if there exists a record with the same entity_key value.

In your case,  a value 'abc' is used as entity_key for 2 records and therefore 2nd insert fails with "Entry With This Key Already Exists".

 

So, in short, Composite primary key needs to be a unique value.

 

Thanks,

C
CarlesColl1-VisitorAnswer
1-Visitor
January 11, 2019

You should create a "fake" primary key on inserting, like an autoincrement field ( or use a GUID value ), or also on your case ( if col1 / col2 length has maximum length, say 5 ) you can create the fake primery key as:

 

var fakePrimaryKeyColumn = (("00000"+col1).slice(-5)+("00000"+col2).slice(-5));

 

 

    K
    knakaya6-ContributorAuthor
    6-Contributor
    January 15, 2019

    Thanks for the replies.

     

    I would use "fake" primary key with fixed length.

    Terms & ConditionsAccessibility statement