AppsAutessaDB

Tables and data modeling · AutessaDB

Foreign keys and joins

AutessaDB supports relations: an Integer column can be a foreign key to a row in another table, and a join query follows those links with filters across the joined tables. Each joined table keeps its own security, so a join never shows a row the caller could not read on its own.

A database essential, built in
customers3 columnsSAMPLE
idINTEGERPK
nameSHORT_TEXT
regionSINGLE_SELECT
orders3 columnsSAMPLE
idINTEGERPK
customer_idINTEGERFK → customers
totalDECIMAL
join-query resultorders ⋈ customersSAMPLE
orders.id  customers.name       total
5531       Harbor Freight Co.   1,240.00
5534       Larkspur Clinic        318.50
where customers.region = 'west'

-- the problem

Real data is related, and duplicating it goes stale

Customers have orders, tickets have comments, contracts have clauses. Copying a customer name into every order means fixing it in a hundred places later. Foreign keys keep one source of truth and let queries pull related rows together.

What you get

  • Any Integer column can be a foreign key that points at a row in another table.
  • Foreign-key columns are always indexed, so filtering on them stays fast.
  • Join queries take a main table, a list of joins and optional WHERE groups applied after the join.
  • A join needs READ on every table in it.
  • A reference to a row the caller cannot see is treated as if that row does not exist.
  • The builder query panel can add joins and sorts without writing any request by hand.

-- how it works

How does foreign keys and joins work in AutessaDB?

  1. Add the link

    On an Integer column, choose the table it points to. The column now holds the id of a row there.

  2. Write rows

    Insert child rows with the parent id in the foreign-key column, by hand, from a script or from an agent.

  3. Join

    Run a join query from the builder, the CLI (query join-query) or the API, with filters on the joined result.

-- every option

Join query options

optionwhat it does
tableIdThe main (left) table.
joinsThe tables to join and their join conditions.
whereGroupsOptional filters applied after the join, in the standard condition format.
groupsLogicalOperatorHow the filter groups combine: AND or OR.
PermissionREAD on every table in the join.

-- example

Follow foreign keys from the CLIbash
# follow foreign keys
autessa autessadb query join-query --tableId ayXduiaLeFKh --json '{...}'

# run each command's help for its exact argument shape
autessa autessadb query join-query help

Full reference in the AutessaDB CLI docs and the API reference.

-- permissions

Who can do what?

accesscan
ADMINAdd or remove foreign-key columns as part of the schema.
READ (on each table)Run join queries across those tables.

-- relations

How it connects

autessadb feature

Tables and column types

Foreign keys are an option on Integer columns.

autessadb feature

Row-level security

Each joined table applies its own row rules.

autessadb feature

Filters and pagination

The same condition format filters joined results.

-- faq

Questions about foreign keys and joins

Does AutessaDB support foreign keys?

Yes. An Integer column can be a foreign key to a row in another table, and those columns are always indexed.

Do joins respect row-level security?

Yes. A join needs READ on every table in it, each table applies its own row rules, and a referenced row the caller cannot see is treated as if it does not exist.

Can I join tables in the data lake?

Yes, with SQL. Data lake tables join in read-only SQL in the SQL Workbench, and the semantic layer documents how they relate.

$ autessa autessadb tables create

Try foreign keys and joins in AutessaDB

Integer columns can be foreign keys to rows in another table, and join queries follow them with filters, while each joined table keeps its own security.

Read the AutessaDB guide or install the CLI.

100% customizable, code and all

Make AutessaDB fully your own.

Use it out of the box, then change anything. Ask Autobot, Autessa’s AI builder, for a new field, screen, report, workflow or agent and it builds it into your copy of the app. Or open the code and edit it yourself. Nothing is locked: the app, its data and its code are yours.

Start free and make it yours