# Foreign keys

_This page explains how to establish a permanent relationship between two tables with a foreign key: creating one, querying across it with dot notation, chaining several, enforcing referential integrity, and when to prefer it over a join._

Where a relationship between two tables is permanent and well defined, declaring it once removes the work of rebuilding it in every query — see [relationships between tables](overview.md) for why, and for how a foreign key compares with the alternative.

A foreign key is an **enumeration over the key column of a keyed table**. The child column stores indexes into the parent's key rather than the values themselves, which is what makes both the lookup and the storage cheap.

## The sample data

The examples use the [Datagen module](../../../modules/datagen/overview.md), which generates a `trade` table and a `master` table of instrument reference data:
```q
q)([getInMemoryTables; buildPersistedDB]): use `kx.datagen.capmkts
q)(trade; quote; nbbo; master; exnames): getInMemoryTables[]
```

`master` is already **keyed on `sym`**, which is what a foreign key requires of its parent:
```q
q)keys master
,`sym
q)5#master
sym | description                          cusip     securityType testFlag ex..
----| -----------------------------------------------------------------------..
AAPL| "Apple Inc."                         037833100 A            0        Q ..
AGEN| "Agenus Inc."                        00847G804 A            0        Q ..
AIG | "American International Group, Inc." 026874784 A            0        N ..
AMD | "Advanced Micro Devices"             007903107 A            0        Q ..
AMTM| "Amentum Holdings, Inc."             023939101 A            0        N ..
```

Every symbol traded appears in `master`, so the enumeration succeeds for every row:
```q
q)all (distinct trade`sym) in exec sym from 0!master
1b
```

## Create a foreign key

Cast the child column to the parent table's name with [`$`](../../../ref/enumerate.md):
```q
q)update sym:`master$sym from `trade
`trade
```

[`meta`](../../../ref/meta.md) records the relationship in its `f` (foreign key) column, and the column's type has become an enumeration:
```q
q)meta trade
c    | t f      a
-----| ----------
sym  | s master
time | n        s
price| f
size | j
stop | b
cond | c
ex   | s
q)type trade`sym
20h
```

You can also declare a foreign key when you define the table, so every insert is checked from the start:
```q
q)t:([] time:`timespan$(); sym:`master$(); price:`float$())
q)`t insert (0D09:30; `AAPL; 255.91)
,0
```

> **Note — Enumerating drops attributes**
>
>
> `trade`'s `sym` column carried a [grouped attribute](../../../ref/set-attribute.md#grouped-and-parted) before the update, and the `a` column above shows it is gone. Re-apply any attribute you depend on after creating the key.
>
## Query across the key

The point of the key is **dot notation**: the parent's columns can be read through the child column as though they were part of the child table.
```q
q)4#select vwap:size wavg price, name:first sym.description, lot:first sym.roundLot by sym from trade
sym | vwap     name                                 lot
----| -------------------------------------------------
AAPL| 266.9065 "Apple Inc."                         100
AGEN| 4.191792 "Agenus Inc."                        100
AIG | 84.56125 "American International Group, Inc." 100
AMD | 174.3745 "Advanced Micro Devices"             100
```

No join appears in that query, and none is performed: `sym.description` is an index into `master`.

### Chain several keys

A column can carry only one foreign key at a time — declaring a second replaces the first. To reach further, chain the keys: give the _parent_ a foreign key of its own and follow both hops with compound dot notation.

`master` records each instrument's listing exchange as a single character, and the `exnames` dictionary maps exchange codes to names. Turn that dictionary into a keyed table, and point `master` at it:
```q
q)listing:([lex:key exnames] name:value exnames)
q)update lex:`listing$`$enlist each ex from `master
`master
```

One query reaches from `trade` through `master` to `listing`:
```q
q)select trades:count i by listedOn:sym.lex.name from trade
listedOn                 | trades
-------------------------| ------
"Cboe BZX Exchange"      | 105
"NASDAQ Stock Exchange"  | 1458
"NYSE Arca"              | 157
"New York Stock Exchange"| 822
```

## Referential integrity

Because the child stores an index rather than a value, the parent's key column becomes load-bearing for every table that references it.

An enumeration that finds no match fails rather than inserting bad data, which is the protection a foreign key buys you:
```q
q)`trade insert (`NOSUCH; 0D09:30; 1.0; 100; 0b; " "; `N)
'cast
```

But the same indirection is dangerous in the other direction. Deleting a row from the parent does **not** invalidate the children — it silently shifts what their indexes point at:
```q
q)2#trade                              / two AAPL trades
sym  price
-----------
AAPL 255.91
AAPL 256.17
q)delete from `master where sym=`AAPL
`master
q)2#trade                              / the same rows now read as AGEN
sym  price
-----------
AGEN 255.91
AGEN 256.17
```

> **Danger — Never reorder or delete rows in a referenced key column**
>
>
> Modifying, reordering, or deleting rows of a parent's key column silently corrupts every table that references it, with no error to warn you. Append to a parent table; do not rewrite it. If you must remove rows, rebuild the children's keys afterwards.
>
## Compound foreign keys

Where the relationship needs more than one column, key the parent on all of them and enumerate the child against the combination.

`trade` records both the instrument and the venue it traded on. A table of per-instrument, per-venue statistics keyed on `sym` and `ex` is therefore a natural parent. Derive one from `quote`:
```q
q)venue:`sym`ex xkey select avgSpread:avg ask-bid by sym,ex from quote
q)3#0!venue
sym  ex avgSpread
-----------------
AAPL C  0.91
AAPL D  1.010882
AAPL I  1.037647
```

Build the compound key by stitching the two child columns into pairs with the [each-both iterator](../../../ref/maps.md#each) and enumerating the result:
```q
q)update venueKey:`venue$(trade[`sym],'trade[`ex]) from `trade
`trade
q)meta trade
c       | t f     a
--------| ---------
sym     | s       g
time    | n       s
price   | f
size    | j
stop    | b
cond    | c
ex      | s
venueKey| j venue
```

The new column holds a row index into `venue`, so its `meta` type is a long rather than a symbol. Dot notation works exactly as in the simple case:
```q
q)5#select trades:count i, spread:first venueKey.avgSpread by sym,ex from trade
sym  ex| trades spread
-------| ----------------
AAPL C | 3      0.91
AAPL I | 4      1.037647
AAPL J | 2      1.011724
AAPL M | 9      0.9361364
AAPL N | 6      0.9583871
```

> **Warning — Index the table explicitly when building the pairs**
>
>
> Inside q-sql, `,'` on two bare column names doesn't produce the pairs you want:
>
> ```q
> q)update venueKey:`venue$sym,'ex from `trade
> 'length
> ```
>
> Refer to the columns through the table — `` trade[`sym],'trade[`ex] `` — as above.
>
You can also declare a compound key with the table. Then every insert must supply the enumerated value:
```q
q)t:([] time:`timespan$(); sym:`$(); ex:`$(); price:`float$(); venueKey:`venue$())
q)`t insert (0D09:30; `AAPL; `N; 255.91; `venue$(`AAPL;`N))
,0
```

## Remove a foreign key

[`value`](../../../ref/value.md) resolves the enumeration back to its values:
```q
q)update sym:value sym from `trade
`trade
```

For a table with several keys, this helper finds every column that has one and applies `value` to each:
```q
q)removeKeys:{![x;();0b;tr!value,/:tr:?[meta x;enlist(<>;`f;(),`);();`c]]}
q)meta removeKeys trade
c    | t f a
-----| -----
sym  | s
price| f
```

On a **compound** key, `value` removes the table mapping but leaves the column as the list of row indexes it was — there are no original values to restore, because the key replaced two columns with one index.

## Foreign keys or a join?

Both produce the same answer; they differ in when the mapping is built.

- A **[join](../../../ref/joins.md)** such as [`lj`](../../../ref/lj.md) builds the mapping at query time, and expands the parent to the length of the child before the result columns are selected. Every query pays that cost again.
- A **foreign key** builds the mapping once, when the data is written. The query then does an index lookup, and touches only the parent columns it names.

So a relationship that is permanent, queried repeatedly, and used by many queries favors the foreign key; a one-off enrichment favors a join.

The [foreign keys white paper](https://code.kx.com/q/wp/foreign-keys/) measured both on a million-row table. Reading one reference column through a **single-column** relationship, the foreign key ran in roughly half the time of the equivalent [`lj`](../../../ref/lj.md) and used about 43% less memory — the join has to expand the parent to the child's length before selecting, and the foreign key doesn't.

Across **two columns** the difference was far larger: the paper reports the compound key resolving some 37 times faster than the two-column join, because a compound key is still one index lookup however many columns it spans.

Foreign keys also make normalization cheap. Reference data lives once in the parent, and the children hold indexes rather than copies, so there is no redundancy to keep consistent.

How much any of this matters depends on your data, your query shapes, and how much of the parent a query touches. Measure it on your own data with [`\ts`](../../../ref/syscmds.md#ts-time-and-space), which reports both time and space:
```q
q)\ts select time,sym,sym.description from trade
```

## Limitations

- **A column can hold only one foreign key.** Declaring a second replaces the first; [chain keys](#chain-several-keys) to reach further.
- **The parent must be a keyed table.** Splayed tables cannot be keyed, so a foreign key cannot be persisted with one.

That second limitation is the important one in practice. A foreign key works in memory, but any on-disk database of consequence is [splayed](../splayed-tables.md) or [partitioned](../partition.md). Neither can be keyed. On disk, the mechanism is a [link column](linking-columns.md) instead. See [relationships between tables](overview.md) for the full comparison.

## Next steps

- Persist the same kind of relationship on disk with [linking columns](linking-columns.md).
- Read how enumerations work underneath in [enumerations](../../../ref/enumerations.md), [`$`](../../../ref/enumerate.md), [`!`](../../../ref/enumeration.md), and [`?`](../../../ref/enum-extend.md).
- Compare with joining at query time in [joins](../../../ref/joins.md).
