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:
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 NamesEach 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 childrenfor 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 NamesIn 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:
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 defaultEmployees_List_1 // "_1" appended to disambiguate the second to-children relationship name