Cannot insert two records has two primary key.

[knakaya]

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?

[tkawasaki]

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,

[CarlesColl · Accepted solution]

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));

[knakaya]

Thanks for the replies.

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