Skip to content

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 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, which generates a trade table and a master table of instrument reference data:

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)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 will succeed for every row:

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 $:

q)update sym:`master$sym from `trade
`trade

meta now records the relationship in its f (foreign key) column, and the column's type has become an enumeration:

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

A foreign key can equally be declared when the table is defined, so that every insert is checked from the start:

q)t:([] time:`timespan$(); sym:`master$(); price:`float$())
q)`t insert (0D09:30; `AAPL; 255.91)
,0

Enumerating drops attributes

trade's sym column carried a grouped attribute 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)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)listing:([lex:key exnames] name:value exnames)
q)update lex:`listing$`$enlist each ex from `master
`master

Now one query reaches from trade through master to listing:

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)`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)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

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 rows must be removed, 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, so a table of per-instrument, per-venue statistics keyed on sym and ex is a natural parent. Derive one from quote:

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 and enumerating the result:

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)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

Index the table explicitly when building the pairs

Inside q-sql, ,' on two bare column names does not produce the pairs you want:

q)update venueKey:`venue$sym,'ex from `trade
'length

Refer to the columns through the table — trade[`sym],'trade[`ex] — as above.

A compound key can also be declared with the table, in which case every insert must supply the enumerated value:

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 resolves the enumeration back to its values:

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)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 such as lj 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 favours the foreign key; a one-off enrichment favours a join.

The foreign keys white paper 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 and used about 43% less memory — the join has to expand the parent to the child's length before selecting, and the foreign key does not. 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, so measure it on your own data with \ts, which reports both time and space:

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 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 or partitioned, and neither can be keyed — so on disk the mechanism is a link column instead. See relationships between tables for the full comparison.

Next steps