Relationships & Foreign Keys

Relationships (refs) define how tables connect to each other through foreign keys. Ainecto supports creating refs from the Refs tab, from the table editor, and through automatic FK suggestions.

---

Create a relationship from the Refs tab

1. Open the Refs tab in the left sidebar
2. Click + Add Ref
3. Select the from table and column
4. Select the to table and column
5. Choose the relationship type
6. Click Save

The new relationship line appears on the canvas connecting the two tables.

---

Create a relationship from the table editor

When editing a table, scroll to the Foreign Keys section at the bottom of the edit panel.

1. Click + Add Foreign Key
2. Select the referenced (target) table
3. Map the source column to the target column
4. Choose the relationship type, On Delete, and On Update actions
5. Click Save at the bottom of the edit panel

All FK changes in the table editor use a pending model — changes are queued until you press Save, which commits the table and all FK changes atomically.

---

FK auto-suggestion

Ainecto automatically detects column names that look like foreign keys and suggests relationships.

How it works

When a column name matches a common FK pattern, a suggestion icon appears next to the column in the Foreign Keys section:

Column pattern	Suggested target
user_id	users.id
order_id	orders.id
category_id	categories.id
parent_id	Same table .id (self-ref)

The pattern matcher strips the _id suffix and looks for a table whose name matches the prefix (with plural handling).

Accept a suggestion

Click Add next to the suggestion to create a pending FK. The suggestion moves into the pending changes list. Press Save to commit it along with any other table changes.

---

Relationship types

Symbol	Type	Meaning
>	Many-to-One	Many rows in the source table reference one row in the target
<	One-to-Many	One row in the source table is referenced by many rows in the target
-	One-to-One	Each row in the source maps to exactly one row in the target

The relationship type determines the cardinality notation displayed on the connection line on the canvas.

---

Pending changes model

Foreign key edits in the table editor follow a pending workflow:

1. Add a new FK — appears as a pending addition
2. Edit an existing FK — the change is staged
3. Delete an FK — marked for removal
4. Save — all pending additions, modifications, and deletions are committed together

This ensures that related changes (e.g., renaming a column and updating its FK) are applied atomically.

---

On Delete / On Update actions

Each relationship supports referential actions:

Action	Behavior
No Action	Reject the delete/update if referenced rows exist (default)
Cascade	Automatically delete/update referencing rows
Set Null	Set the FK column to NULL
Set Default	Set the FK column to its default value
Restrict	Same as No Action but checked immediately

Set these options when creating or editing a relationship.

---

Edit or delete a relationship

• Edit: Click on a relationship line on the canvas, or find it in the Refs tab and click to open the editor
• Delete: Select the relationship and press Delete/Backspace, or use the delete button in the edit panel

---

Tips

• Use the FK auto-suggestion to speed up initial schema design — follow naming conventions like table_id for automatic detection
• The pending model prevents partial saves — if you change your mind, close the edit panel without saving to discard pending changes
• On Delete Cascade is useful for child tables (e.g., order_items when orders is deleted), but use it carefully on shared reference tables