Manage Relationships

You can traverse to the other side of a relationship by double-clicking the relationship in the Relationships to Parent (or Relationships to Children) list.
lac52
You can manage relationships using API Creator, using Data Explorer, or by way of 
@tables
@columns
, and 
@relationships
 managed data REST endpoint requests. This article addresses how to manage relationships using API Creator. Physical relationships are the relationships that are defined in your database. Virtual relationships are the relationships that you add in API Creator.
For more information:
In this article:
 
 
Access your Relationships
The physical and virtual relationships are available on the Schema page. With your API open, in the Create section, click 
Schema
.
Add a Relationship
Associating a parent entity (table or view) with a child entity adds a relationship between those entities. The related entities can be in the same database or in different databases.
You can:
  • Add relationships using API Creator or using PUT/POST JSON mechanisms. This procedure explains how to add relationships using API Creator. Adding a relationship using API Creator adds a virtual relationship.
    For more information about an example of how to add a relationship using the PUT JSON mechanism, see the relationship examples in PUT.
  • Add virtual relationships across schemas by adding relationships between databases.
    When you add a relationship between databases, 
    Layer7 Live API Creator
     reloads the schema. The time that it takes API Creator to read the metadata depends on the size of the database and the latency to the database server.
    For more information about how to integrate data, see Integrate Data and Systems.
  • Add relationships and define relationship names for managed databases and databases that are not managed.
Follow these steps:
 
  1. With your API open, in the Create section, click 
    Schema
    .
    The Tables tab displays by default. A list of to-parent relationships display in the Relationships to Parent list. A list of to-children relationships display in the Relationships to Children list.
  2. Above the Relationships to Parent (or Relationships to Children) list, click 
    Add
    .
    The Add Relationship window opens.
  3. Complete the following fields, and then click 
    Add
    :
    Parent Entity
     
    Defines the parent side of the relationship. The parent entity is the entity (often a SQL table or view) which conceptually contains or owns the children rows. For example, the 
    Company
     parent entity contains the 
    Department
     child entities. There can be more than one department per company.
    Child Entity
     
    Defines the child side of the relationship. The child entity is the entity which conceptually is contained or owned by the parent entity. For example, the 
    Company
     parent entity contains the 
    Department
     child entities.
    To-Parent Name
     
    The relationship name from the child entity to the parent entity. Enter a relationship name that describes the connection between the parent and child entity, from the perspective of the child entity looking up to the parent entity. For example, enter 
    Company
     as the relationship name to define the relationship between the 
    Company
     parent entity and the 
    Department
     child entity.
    Unique:
     Yes
    For more information about how 
    Layer7 Live API Creator
     generates the to-parent relationship name, see Database Relationships.
    To-Children Name
     
    The relationship name by which the parent entity accesses a list of children entities. This relationship name is the connection between the parent and child entity, from the perspective of the parent entity looking down to the child entity. You can rename this relationship to something meaningful in the context of your business logic. For example, enter 
    Department_List
     as the to-children relationship name to define the relationship between the 
    Company
     parent entity and the 
    Department
     child entity.
    Unique:
     Yes
    For more information about how 
    Layer7 Live API Creator
     generates the to-children relationship name, see Database Relationships.
    Parent Cascade Actions
     
    Determines what happens when you update the parent row's foreign key.
    On Update
     
    Determines what happens to the children rows' foreign key when you update the parent row's foreign key.
    Options:
     
    • Cascade:
       The children rows' foreign keys inherit the updated parent row's foreign key.
    • Set Null:
       The children rows' foreign keys are set to null.
      Choosing this option results in an error if the foreign key cannot be nulled.
    • Restrict:
       If the children rows' foreign key has children rows, you cannot update the parent row's key.
    Default:
     Restrict
    On Delete
     
    Determines what happens to the children rows' foreign key when you delete the parent row's foreign key.
    Options:
     
    • Cascade:
       The children rows' foreign keys are deleted to reflect the parent row deletion.
    • Set Null:
       The children rows' foreign keys are set to null.
      Prerequisite:
       The foreign key must be nullable.
    • Restrict:
       You cannot delete the parent row if it has children rows.
    Default:
     Restrict
    Parent Column
     
    Defines the pairs of columns from each entity. Define one or more pairs of columns, one from each entity, by clicking the plus symbol. The pairs become the join columns and are used to join the entities.
The virtual relationship is added and displays in the Relationships to Parent (or Relationships to Children) list. The relationship name that defines the relationship displays with the relationship. 
Relationships are indicated with arrows to the left of relationship name in the relationship lists on the Schema page. The arrows are solid or outlined and point upward or downward. For example, virtual to-parent relationships are indicated with an outlined arrow that points upward in the Relationships to Parent list.
For more information about the arrows, see the legend at the bottom of the Schema page in API Creator or see the definitions of relationships in Database Relationships.
Update a Relationship
You can update your relationships, such as overriding the relationship name with a meaningful name relevant to your application domain. Meaningful relationship names can help improve the readability of your custom logic code that requires access to parent and child entities.
The following image shows an example of physical relationships in the Sample API. Because there are multiple relationships from the 
departments
 parent entity and the 
employees
 child entity, the relationships have been updated with meaningful relationship names:
 edited.png 
 Changing the relationship name can invalidate JavaScript code and rules that use the relationship name.
Best Practice:
 Update relationship names before creating rules or adding JavaScript code.
The changes that you make to relationships are saved in the log.
If you remove a foreign key that is defined in your database, 
Layer7 Live API Creator
 remembers the relationship and 
Layer7 Live API Creator
 reports the key removal as an issue. You can view these issues by verifying your API.
Follow these steps:
 
  1. Update your relationship. On the Tables tab, above the Relationships to Parent (or Relationships to Children) list, select the physical relationship you want to update, and then click 
    Update
    .
  2. Identify any issues in rules and resources that are affected by the relationship name change by verifying your API. Update them accordingly. If you are using Data Explorer, repair Data Explorer.
    For more information about how to verify your API, see Manage API Issues.
  3. If you have defined sum rule types in your API, update the rule types to use the new relationship name.
  4. Verify the JavaScript code that reference entities that use the relationship name and update them accordingly.
Traverse a Relationship
You can traverse to the other side of a relationship by double-clicking the relationship in the Relationships to Parent (or Relationships to Children) list.
Reset a Relationship
You can delete only virtual relationships. You can return physical relationships to their database-derived value.
Follow these steps:
 
  1. On the Tables tab, above the Relationships to Parent (or Relationships to Children) list, select the physical relationship you want to update, and then click 
    Update
    .
    The Update Relationship window opens.
  2. Click 
    Reset
    .
The physical relationship is reset to its database-derived value.
Interact with Data in Multiple Databases
When you create an API and connect API Creator to your database, 
Layer7 Live API Creator
 reads the schema to build (not code generation) the JavaScript object model. This object model provides access to attributes and related objects.
If your API is connected to more than one active database, the data source prefix specifies the database that 
Layer7 Live API Creator
 should use, for instance, when defining resources, rules, and JavaScript.
You interact with data in multiple databases using the following syntax:
<data source prefix>:<table name>
Example:
main:Customer
For more information: