cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Cannot insert two records has two primary key.

SOLVED
Level 7

Cannot insert two records has two primary key.

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Cannot insert two records has two primary key.

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

 

 

3 REPLIES

Re: Cannot insert two records has two primary key.

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,

Highlighted

Re: Cannot insert two records has two primary key.

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

 

 

Re: Cannot insert two records has two primary key.

Thanks for the replies.

 

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