Composite keys in an entity type

#1

Is it possible to have composite keys (i.e. id1, id2, … idn) in an entity type?

0 Likes

#2

I tried making the id a stored calc field but it did not help:

entity type AwesomeType schema name 'AWESOME_TYP' {
  id1: string
  id2: string
  /**
   * Composed key
   */
  id: string stored calc "concat(id1,'_', id2)"
}

Then when I try to create an instance I see the following error: null value in column "id" violates not-null constraint:

var a = AwesomeType.create({id1: '111', id2: 'ok'})
Error: Write failed: Unable to create record for type AwesomeType with id 503807b7-0082-4cae-8f8e-5eb282e1be2c: Batch entry 0 INSERT INTO C3_2_AWESOME_TYP (ID1_S,ID2_S,CREATED,CREATED_I,UPDATED,UPDATED_I,META_TIMESTAMP_T,META_TIMESTAMP_I,TENANT_TAG_ID,CREATED_BY,UPDATED_BY,VERSION) VALUES ('111','ok','2018-01-16 19:27:22+00',0,'2018-01-16 19:27:22+00',0,'2018-01-16 19:27:22+00',0,3,'BA','BA',1) was aborted: ERROR: null value in column "id" violates not-null constraint↵  Detail: Failing row contains (3, null, 1, 111, ok, null, 2018-01-16 19:27:22, 0, BA, 2018-01-16 19:27:22, 0, BA, 2018-01-16 19:27:22, 0, null, null, null, null).  Call getNextException to see other errors in the batch.↵ERROR: null value in column "id" violates not-null constraint↵  Detail: Failing row contains (3, null, 1, 111, ok, null, 2018-01-16 19:27:22, 0, BA, 2018-01-16 19:27:22, 0, BA, 2018-01-16 19:27:22, 0, null, null, null, null)
0 Likes

#3

What would you like to achieve?

You can always concatenate two fields for the id at transform time if the type is created during data load or in a js function if the type is populated after data load.

If you want to have unicity constraint based on fields id1, id2, … you can add a db annotation above your type such as:
@db(unique=[“id1, id2”])

that would mean: I only want one instance of AwesomeType in the db for a given value of id1 AND id2.

0 Likes

#4

Thanks for pointing to the unicity constraint, I’m not handling this properly!
The type’s instances are result of a MapReduce job, as you suggest I can manually concat (or apply any other function) the fields to create the id.
But I was wondering if there is a way to do get the system do it for me (i.e. automatically like with calc fields)

0 Likes

#5

A common way to do this would be to concatenate the fields during data load/transformation.

You can’t set the id AFTER the object already exists… b/c you can’t change the id of an existing object, you can only create a new object.

There is also no concept of “virtual ids”

0 Likes