Create and edit relationships

A relationship links a field of one Lake table to a field of another on the relationship diagram. Create it by dragging between fields or in the Create Relationship dialog, change its cardinality at any time, and remove it when the link no longer applies. Custom arrangements of the canvas are covered in Diagram layouts.

Create a relationship by dragging

1. Hover over the row of the field you want to link. Handles appear on the row.
2. Drag from a handle to the field you want to link to. A dashed line follows the pointer and snaps to a field when it is close.
3. Release over the target field.

Releasing starts the save. A new line appears on the canvas straight away and is replaced by the saved one when the save completes. The diagram then shows "Relationship created" with "Table reference and relationship created successfully".

• A dragged relationship starts as One to Many, with the field you dragged from as the primary side. Change it afterwards in Set Relationship Type.
• A dragged link joins two different tables: a drag between two fields of one table is ignored. (The Warehouse diagram allows a field to reference another field of the same table; see Relationship diagram.)
• While a connection is being saved, the diagram ignores a repeated drag of the same connection.
• If the save fails, the line disappears and the diagram shows "Failed to create reference" with "Unable to create table reference". Try the drag again.

Create a relationship in the dialog

1. Right-click the canvas and choose New relationship. With the focus inside the diagram, you can also press Alt+N; the menu shows the shortcut beside the item.
2. In Create Relationship, choose the Primary Table and then its Primary Field.
3. Choose the Referenced Table and then its Referenced Field.
4. Choose a Cardinality. The dialog opens on One to One every time.
5. Click Create.

• Each field choice shows the field's type in brackets.
• Create stays unavailable until all four choices are made and the two fields have the same type. When they differ, the dialog shows "Relationships can only be created between fields of the same type".
• Until you choose, the lists read Select primary table, Select primary field, Select table, Select field and Select cardinality. An empty list reads No tables found or No fields found.
• When the save finishes, the new line appears selected on the canvas and the view fits both tables on screen.

How cardinality works

Cardinality says how many rows of each table a relationship matches.

• Many-to-many is not offered. To model one, put a third table (a junction table) between the two tables and link both of them to it.
• A field that takes part in a relationship carries a small mark at the edge of its row: an asterisk marks the many side and a minus the one side.

Change a relationship's cardinality

1. Click the relationship line to select it. Its tools show an edit button and a delete button.
2. Click Edit relationship, or double-click the line.
3. In Set Relationship Type, choose the new cardinality. The dialog opens on the relationship's current type.
4. Click Confirm. The dialog reads "This will confirm the relationship between the nodes you selected".

The change is saved when you confirm. If it fails, the diagram shows "Failed to update relationship" with the reason.

Remove a relationship

1. Click the relationship line to select it.
2. Click Delete relationship.
3. In Remove Relationship, click Remove.

The line leaves the canvas and the diagram shows "Relationship Removed". If the removal fails, it shows "Reference remove failed" with the reason.

Removing a relationship removes the link between the two fields only: the tables and their data stay as they are.

Who can change relationships

Creating, changing and removing a relationship needs Add/Modify Lake Objects. When the server refuses a change, the diagram shows the failure message with the reason. For the full permission model, see Who can do what in the Lake.

Questions, answered

Why is the Create button greyed out?

Create stays unavailable until all four choices are made (the two tables and their fields) and the two fields have the same type. If the types differ, the dialog shows "Relationships can only be created between fields of the same type". Choose two fields of one type to continue.

Why did nothing happen when I dragged between two fields of one table?

On the Lake diagram, a dragged link must join two different tables, so a drag inside one table is ignored. The Warehouse diagram allows a field to reference another field of the same table; see Relationship diagram.

Can I create a many-to-many relationship?

No. The offered cardinalities are One to One, One to Many and Many to One. To model a many-to-many link, add a third table (a junction table) between the two tables and link both of them to it.

Can I change a relationship's cardinality after creating it?

Yes. Click the line, click Edit relationship (or double-click the line), choose the new cardinality in Set Relationship Type and click Confirm. The dialog opens on the relationship's current cardinality.

How do I remove a relationship?

Click the line to select it, click Delete relationship, then click Remove in Remove Relationship. The diagram shows "Relationship Removed".

Does removing a relationship delete the tables or their data?

No. Removing a relationship removes the link between the two fields only. The tables and their rows stay as they are.

What do the small marks beside a field mean?

A field that takes part in a relationship carries a mark at the edge of its row: an asterisk for the many side and a minus for the one side.

What happens if a relationship fails to save?

The diagram shows "Failed to create reference" with "Unable to create table reference", and the new line disappears. Try the drag or the dialog again. A failed cardinality change shows "Failed to update relationship", and a failed removal shows "Reference remove failed".

What permission do I need to change relationships?

Add/Modify Lake Objects. Creating, changing and removing a relationship all need it; without the permission the change is refused and the diagram shows the failure message with the reason. See Who can do what in the Lake.

Can I create a relationship without the dialog?

Yes. Hover over a field's row and drag from a handle to the field you want to link. A dragged link starts as One to Many; change it afterwards in Set Relationship Type.