Point a foreign key to a unique key instead of the primary key

By default, the destination key columns are the primary key of the parent entity. In some models, you want the foreign key to point to a business identifier instead.

 

Example: Order line and Product

Let's add a Product entity to the example, and link it to the Order line entity of Derive identifying conceptual relationships. The Product entity has a technical primary key id (a UUID) and a business identifier SKU, defined as unique. Each order line is for exactly one product. To point the foreign key to SKU instead of id, select SKU in Attribute(s) of Product, and leave Attribute(s) of Order line empty:

 

 

After derivation, the foreign key column Product_SKU is added to the Order line table, with Product.SKU as destination key column. With the naming conventions of the PostgreSQL model, Product_SKU becomes product_sku:

 

 

This is the same approach as for associative tables that use non-key attributes.

 

 

How the destination key attributes are chosen

The destination key attributes are chosen in this order:

• the attributes you selected for the parent entity in the Foreign Key constraint section, if any,
• otherwise its primary key. With a composite primary key, one foreign key attribute is added to the child entity for each attribute of the key, and one Foreign Key constraint is created on all of them together,
• otherwise, when the parent entity has no primary key, Hackolade Studio creates an attribute id in it as primary key, which then becomes the destination key attribute. This is the same rule as for associative tables.