Tilda JSON Syntax: Object Foreign Key

<-- Object Syntax

Foreign Keys

Foreign keys define relationships between the column(s) in one object, and the Primary Key column(s) in another object.

// Within an Object definition, you can define any number of foreign keys (or none).
 "foreign":[
      { "name":"Blah", "srcColumns":["colA", "ColB"], "destObject":"SomeTable" }
  ]

The fields are:

• name: The name of the foreign key (most database support naming foreign keys)
• srcColumns: A list of 1 or more columns in the Object that represent the foreign key
• destObject: The name of the destination object to which this foreign key refers to.

🎈 NOTE: The srcColumns must match exactly in order and type the definition of the primary key for the destination object. So, for example:

// Create a facility object representing a hospital, with a custom PK consisting of 2 string
// columns orgId and facilityId.
 { "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" }
 }

// A clinician who belongs to a particular hospital, i.e., defines a foreign key.
,{ "name": "Clinician"
  ,"description": "blah blah"
  ,"columns":[
      { "name":"orgId"     , "sameas":"Facility.orgId"     , "description":"System organization id" }
     ,{ "name":"facilityId", "sameas":"Facility.facilityId", "description":"System facility id"     }
     ,{ "name":"fullName"  , "type"  :`"STRING", "size":250, "description":"First/Last name"        }
   ]
 ,"primary": { "autogen":true }
 ,"foreign": [
     { "name": "Facility",  "srcColumns":["orgId", "facilityId"], "destObject": "Facility" }
   ]
}

• The Facility object defines its custom key as consisting of 2 columns. Note that as primary key columns, they must both be defined as invariant:true and nullable:false.

• The Clinician object defines the "orgId" and "facilityId" columns and make use of the "sameAs" feature to pick up type information from the source columns from Facility. The fields Type, Nullable and so on will be borrowed as a result.

• Pay attention to the order of columns: the order defined in the foreign key's srcColumns must match the order of the columns defined in the primary key. Tilda will catch many inconsistencies related to types, but there are several cases where an error is not detectable automatically when columns share the same type as is the case here. If we had had srcColumns":["facilityId", "orgId"], it would be incorrect logically and impossible to catch. Think of it like calling a function with 2 parameters of the same type: a compiler wouldn't know if you switched the input parameter variables around, even if they shared the same name as the function's input parameters.

• Clinician defines an autogen'ed key, so the model will contain an extra column 'refnum' of type LONG, i.e., a 64bit integer.