Derive identifying conceptual relationships
A relationship is identifying when the child entity depends on the parent entity for its identity. An order line, for example, only exists within its order: it is identified by its order together with its own number. A relationship is non-identifying when the child entity has an identity of its own: an order is identified by its own number, whatever the customer who placed it.
You set this with the Constraint type of the relationship, in its Foreign Key constraint section, as described in Constraint type: identifying versus non-identifying relationships. At derivation, Hackolade Studio translates this identity into the primary key: with an identifying relationship, the foreign key attributes are added to the primary key of the child entity.
Example: Order and Order line
Let's continue the example of Derive a one-to-many conceptual relationship, and say that an order line only exists inside its order: Order (1) contains Order line (1..n), and the relationship is identifying. Order line has its own primary key in Polyglot, line number:
After derivation, the Order_id column is added to the Order line table and to its primary key, which is made of line number + Order_id.
Note
If the child entity has no primary key in Polyglot, Hackolade Studio also creates one. Without the line number attribute, the order_id and order_line_id columns are added to the Order line table, and both form its primary key. The generated order_line_id column is needed: with order_id alone as primary key, an order could only have one line.
Important
An identifying relationship is only possible when the parent entity is mandatory, with cardinality 1. A primary key attribute cannot be empty.
Primary key of the child entity
The primary key of the child entity changes as follows:
| Child entity | Non-identifying relationship | Identifying relationship |
|---|---|---|
| Has a primary key | Not changed | The foreign key attributes are added to it |
| Has no primary key, one-to-many | No primary key is created | Foreign key attributes + a generated attribute, order_line_id in the example |
| Has no primary key, one-to-one | No primary key is created | Foreign key attributes only |
Note
A composite primary key or a unique key is only created when the target supports it.
Chains of identifying relationships
Identifying relationships can follow each other. Let's suppose that the relationship between Customer and Order is identifying too. The primary key of the Order table then becomes id + Customer_id, and this complete key must be added to the Order line table, not only id.
Hackolade Studio resolves the chain in order, so that the keys cascade from one table to the next. The key of the Customer table is first added to the Order table, then the key of the Order table is added to the Order line table. The Order_id and Order_Customer_id columns are added to the Order line table, and both become part of its primary key, together with its own line number column.
Warning
A chain of identifying relationships cannot loop back: an entity cannot depend on itself for its identity, directly or through other entities. Such a cycle is a design problem in the Polyglot model, for example a recursive relationship set as identifying, such as a Product that replaces another Product. Hackolade Studio then derives the relationships of the cycle, and those that depend on it, as non-identifying, and reports a warning, as described in Check and update generated foreign keys. The same happens when the parent entity has cardinality 0..1.