Tilda JSON Syntax: Object Primary Key

<-- Object Syntax

Primary Keys

Primary keys are a way to define the identity of an object, either through a numeric value (a surrogate key), or a collection of columns representing a more natural identify of an object. Another way to define identities is to use Unique Indices. Primary keys are most of the time used to identify a unique row in a table, but also to define an anchor for another table to link to via a Foreign Key: the term "relational" in Relational Database refers to this primary/foreign key concept which establishes a relationship between a parent table and one or more children tables.

// Within an Object definition, you can define an automatic key
 "primary": { "autogen":true, "keyBatch":500 }

// You can also define a key explicitly
 "primary": { "autogen":false, "columns":["colA", "colB", "colC"] }

The fields are:

• autogen: whether the primary key should be auto-generated or not.
  • If set to false, you have to explicitly name the columns that represent your primary key. Those columns must be nullable:false and invariant:true. Tilda doesn't allow you to update a primary key once an object has been created and inserted in the data store. If you need a mutating identity (maybe not such a good idea), you can use a Unique Indices instead.
  • If set to true, an extra column called "refnum" of type LONG will be added to the object and the platform makes use of the TILDA.Key table and its helper functions to manage new key values. At this time, the name of the managed primary key column cannot be changed short of declaring and therefore managing such a column manually.
• keyBatch: if autogen:true, configures the size of the batch of keys that should be requested at runtime when new keys are needed. Any key unused will be lost. So for example, what starting, with a batch of 500, the keys 0-499 will be allocated. An application may only use keys 0-200 and get shut down for example. When re-started, another batch 500-999 will be allocated and so on. No special functionality is provided to "recover" lost keys (aka key gaps).
• columns: a list of columns specifying the primary key for the object when autogen:false.

🎈 NOTE: The primary columns, when defined explicitly, must be non-nullable and must be defined as invariant. So, for example:

 { "name": "Facility"
  ,"description": "blah blah"
  ,"columns":[
      { "name":"orgId"     , "type":"STRING", "size":250, "nullable":false, "invariant":true, "description":"System organization id" }
     ,{ "name":"facilityId", "type":"STRING", "size":250, "nullable":false, "invariant":true, "description":"System facility id"     }
    ]
  ,"primary": { "columns":["orgId", "facilityId" }
 }

🎈 NOTE: All objects must have at least one identity. That can be supplied via a primary key as above, or by specifying at least one Unique Index. Tilda will enforce that either one or the other are defined. An object/table can of course have multiple identities.