Schemas

Define relationships and indexes

Relationships and indexes give a schema its shape. Kenseme records primary keys, foreign-key relationships and indexes per table, and can suggest relationships by matching column names to primary keys.

In the app: Data Model › Schemas › your schema › Tables › a table › Relationships or Indexes tab

[screenshot]
A table's Relationships tab, with "Relationships From This Table" above "Relationships To This Table".

Before you start

Changing keys, relationships and indexes needs Read/Write access to the schema.

Set the primary key

  1. Open the table and go to the Columns tab.
  2. Click the pencil icon on a key column.
  3. Tick Primary Key and click Save Changes.
  4. For a composite key, repeat for each column in the key.

The key columns show a key icon in the grid, and the Indexes tab shows the key under Primary Key.

Note: Keys, relationships and indexes are design metadata. Kenseme doesn’t enforce them on data; they flow into the SQL it generates.

Relationships on a table

The Relationships tab has two sections:

  • Relationships From This Table: relationships where this table references another. Columns: References Table, Column Mappings, Type, and edit/delete actions.
  • Relationships To This Table: other tables whose relationships point here. Columns: From Table, Column Mappings, Type.

Create a relationship

  1. Open the table and go to Relationships.
  2. Click Add Relationship. The Create Database Relationship dialog opens in three steps.
  3. Step 1 of 3, source. Pick the Relationship Type: One to Many, Many to One or Many to Many. Pick the From Table and tick its columns. For One to Many, pick the primary key columns; for Many to One, the foreign key columns.
  4. Click Next.
  5. Step 2 of 3, target. Pick the Schema (it can be a different schema), the To Table, and its columns.
  6. Click Next.
  7. Step 3 of 3, map columns. For each source column, pick the matching target column, or choose Literal Value and type a fixed value.
  8. Click Finish.

To change a relationship, click its pencil icon; the Edit Database Relationship dialog uses the same steps. To remove one, click the trash icon. The confirmation says you can restore it within 30 days.

Suggest relationships

Kenseme can suggest relationships by matching column names to the primary keys of other tables. There are two places to run it.

For the whole schema

  1. On the schema’s Info tab, under ACTIONS, click Suggest Relationships.
  2. Kenseme analyzes every table and lists suggestions with One (PK), Column Mappings and Many (FK).
  3. Click Create on a row, or tick rows and click Create Selected. Rows that already exist show Created.

For one table

  1. On the table’s Relationships tab, click Suggest.
  2. Tick All schemas to look beyond this schema, then click Search.
  3. Create suggestions the same way.

If nothing is found, the dialog says so. That usually means the relationships already exist, or column names don’t follow a pattern Kenseme can match.

Tip: Matching works best when foreign key columns are named after the table they point to, for example CustomerId pointing at Customer. Set your ID suffix under naming conventions in Schema settings.

Indexes

The Indexes tab shows the table’s Primary Key and its Indexes, each with Index Name, Type, Columns and Unique.

Create an index

  1. Click Add Index, or tick columns on the Columns tab and click Create Index.
  2. Enter an Index Name, or click the button next to it to generate one from the selected columns.
  3. Optionally add a Description.
  4. Tick Unique Index if the index enforces uniqueness.
  5. In the Columns grid:
    • Tick Indexed for key columns.
    • Tick Included for columns carried in the index but not part of the key.
    • Set Order for the key column sequence and Sort (ascending or descending).
  6. Click Create Index.

At least one column must be marked Indexed. Edit an index with its pencil icon; delete it with the trash icon.

Note: Some dialects don’t support traditional indexes, for example Fabric Lakehouse. Generated SQL leaves them out there. See Dialect-specific output notes.