Database Relationships

A relationship is a one-to-many relationship between two tables in a relational database. They are the associations between a child entity (table or view) and its parent entity. They are a foundation of data modeling. When you connect to a data source,  reads the schema.  adds physical to-parent and to-children relationships for each foreign key it discovers in your database.
lac40
A relationship is a one-to-many relationship between two tables in a relational database. They are the associations between a child entity (table or view) and its parent entity. They are a foundation of data modeling. When you connect to a data source, 
CA Live API Creator
 reads the schema. 
CA Live API Creator
 adds physical to-parent and to-children relationships for each foreign key it discovers in your database.
If your database schema does not define foreign keys, you can add virtual relationships in API Creator. Virtual relationships do not affect the database schema.
Relationships are useful when creating resources that require joins between entities. 
CA Live API Creator
 expresses logic (for example, sum/count and parent references), retrieval joins, and JavaScript programming with the JavaScript object model by referencing relationships.
Relationships are sometimes called 
hasA
 relationships. Parent objects can be related to many child objects. Within a relationship, the table containing the primary key on the "one" side is the parent. For example, in the Sample database, the 
Purchaseorder
 table is a parent to the 
Lineitem
 child table.
In this article:
 
 
Foreign Keys and Relationships
Foreign keys in your database schema can define relationships (physical relationships) or you can add virtual relationship using API Creator. 
CA Live API Creator
 uses these relationship in the resource definition, in rules, in Data Explorer, and in code completion.
Physical Relationships
When you connect your API to a database, 
CA Live API Creator
 generates physical relationships for each foreign key it discovers. Physical relationships are indicated by a solid arrow (⬈ or ⬊) to the left of the relationship names in the Relationships to Parent (or Relationships to Children) list. Solid arrows that point upward (⬈) indicate physical to-parent relationships and are displayed in the Relationships to Parent list on the Tables tab. Solid arrows that point downward indicate physical to-children (⬊) relationships and are displayed in the Relationships to Children list on the Tables tab.
Some databases, such as Microsoft SQL Server, support foreign keys across schemas. 
CA Live API Creator
 scans only one schema at a time for a data source, so it does not discover these foreign keys. When you have multiple data sources defined, there are cross-schema relationships defined in the database, and there is a unique entity in another active data source, 
CA Live API Creator
 creates a virtual relationship. You can also define a virtual relationship between the different database types using API Creator.
For more information about virtual relationships, see the "Virtual Relationships" section.
The following image shows the physical relationships that 
CA Live API Creator
 creates for the Sample API connected to the Sample database:
  SampleDB_DefaultRelationships.png  
You can update the relationship, for example to provide better to-parent/to-children relationship names.
For more information about how to override to-parent and to-children relationship names, see Manage Relationships.
Virtual Relationships
Some databases do not define foreign keys. Since 
CA Live API Creator
 leverages relationships, you can add virtual relationships. Virtual relationships can be between tables in different databases and database types (for example, Oracle and SQL Server). Virtual relationships are indicated by an outlined arrow to the left of the relationship names in the Relationships to Parent (or Relationships to Children) list. Outlined arrows that point upward (⬀) indicate virtual to-parent relationships. Outlined arrows that point downward (⬂) indicate virtual to-children relationships.
How 
CA Live API Creator
 Generates Relationship Names
Each relationship has a to-parent relationship name and a to-children relationship name. 
CA Live API Creator
 generates relationship names as follows:
  • To-Children Name: The name the parent uses to refer to its children
<child entity name> + '_List'
  • To-Parent Name: The name that the child uses to refer to its parent
<parent entity name>
For example, in the Northwind database:
var anOrderList =
aCustomer.Orders_List
; // use to-children relationship name for a list of children
for each (var eachOrder in anOrderList) {
logicContext.logDebug(eachOrder);
}
var aCustomer =
anOrder.Customers
; // use to-parent relationship name to get parent
You can then override these names.
For more information about the Northwind database, see Northwind API Sample.
How 
CA Live API Creator
 Handles Duplicate Child Relationship Names
In some cases, your database might have multiple relationships between the same two tables. For example, the Departments table in the Sample database has two relationships to 
Employees: reportingEmployees
 and 
onLoanEmployees
. In these cases, the defaulted to-parent/to-children relationship names would result in collisions.
For an API where you connect to an existing database, 
CA Live API Creator
 disambiguates the relationship names by using the child's foreign key names. For example, the following image shows the relationships for a new API connected to the Sample database. 
CA Live API Creator
 generated a physical relationship where 
via_on_loan_department_name
 is the foreign key name. The following image shows this example:
  default.png  
For an API created in a new managed database, 
CA Live API Creator
 creates the first relationship using the default syntax. For example, 
<child entity name> + _List
 is the first to-children relationship name and 
CA Live API Creator
 names the subsequent relationships that are defined between the same tables as 
<child entity name> + _List_1/2/3/n
.
For example:
Employees_List // first to-children relationship name as default
Employees_List_1 // "_1" appended to disambiguate the second to-children relationship name