Skip to content

Linking columns

This page explains how to link one table to another with a link column: creating one in memory and on disk, querying across it, linking on several columns at once, the limits to respect, and how to link tables held in separate databases.

A link column holds, for each row of a child table, the row index of the matching row in a parent table — and reads back through dot notation exactly as a foreign key does. Because it indexes an arbitrary column of an arbitrary table rather than enumerating a keyed one, it survives being written to disk, which is what makes it the mechanism for a relationship in a real database. It is also the only option where there is no key column to enumerate at all, including a table that links to itself to represent a parent-child hierarchy.

See relationships between tables for how the two mechanisms compare and when to choose which.

The sample data

The examples use the Datagen module, asking for a splayed master table so that it can serve as the parent of a link on disk:

q)([getInMemoryTables; buildPersistedDB]): use `kx.datagen.capmkts
q)buildPersistedDB["mdb"; ([tbls:`trade; start:2026.04.06; end:2026.04.07; mastertype:`splayed])]

That gives a partitioned trade table and a splayed master table of instrument reference data in the root:

mdb
├── 2026.04.06
│   └── trade
├── 2026.04.07
│   └── trade
├── exnames
├── master
│   ├── .d
│   ├── cusip
│   ├── description
│   ├── description#
│   ├── ex
│   ├── issueprice
│   ├── roundLot
│   ├── securityType
│   ├── sym
│   └── testFlag
└── sym

A link column holds, for each child row, the row number of the matching parent row. Building one is therefore a find of the child's values in the parent's column, wrapped in the Enumeration operator ! to record which table those indexes belong to.

In memory

With an unkeyed parent — here master with its key removed — the link is one update:

q)m:0!master
q)update mlink:`m!(m`sym)?sym from `t
`t

meta reports it in the f column, exactly as it reports a foreign key, and the column type is a long:

q)meta t
c    | t f a
-----| -----
sym  | s
price| f
ex   | s
mlink| j m

On disk, as the data is written

The best time to create a link is while writing each partition, because the link then goes down with the data and needs no later surgery. Datagen can do it directly with the linked parameter:

q)buildPersistedDB["ldb"; ([tbls:`trade`quote; start:2026.04.06; end:2026.04.07; linked:1b; mastertype:`splayed])]

Every partitioned table then carries a link column named after the table it points at:

q)\l ldb
q)meta trade
c     | t f      a
------| ----------
date  | d
sym   | s        p
time  | n
price | f
size  | j
stop  | b
cond  | c
ex    | s
master| j master

To add a link to a database already written, write the column file into each partition and register it in that partition's .d file, which is what tells q the column exists:

q)\l mdb
q)msym:master`sym
q)addLink:{[par]
    d:hsym `$"./",(string par),"/trade/";
    tsym:get ` sv d,`sym;
    (` sv d,`mlink) set `master!msym?tsym;
    .[` sv d,`.d; (); ,; `mlink];
    par}
q)addLink each 2026.04.06 2026.04.07
2026.04.06 2026.04.07
q)get `:./2026.04.06/trade/.d
`sym`time`price`size`stop`cond`ex`mlink

The database must be reloaded for the new column to appear:

q)\l mdb
q)meta trade
c    | t f      a
-----| ----------
date | d
sym  | s        p
time | n
price| f
size | j
stop | b
cond | c
ex   | s
mlink| j master

Write the link per partition, not across them

The loop above treats each partition independently, and it must: a link column records row indexes into the parent, and those indexes cannot cross a partition boundary.

Dot notation reads the parent's columns through the link, with no join in the query and none performed:

q)4#select trades:count i, name:first mlink.description, secType:first mlink.securityType by sym from trade
sym | trades name                                 secType
----| ---------------------------------------------------
AAPL| 1976   "Apple Inc."                         A
AGEN| 2007   "Agenus Inc."                        A
AIG | 2020   "American International Group, Inc." A
AMD | 2012   "Advanced Micro Devices"             A

It works across partitions as well, since each partition's link resolves against the same root-level parent:

q)select trades:count i, lots:first mlink.roundLot by date from trade
date      | trades lots
----------| -----------
2026.04.06| 49947  100
2026.04.07| 50785  100

Unmatched values

Unlike a foreign key, a link column is not an enumeration, so nothing validates it. Find returns the parent's row count for a value it cannot locate — an index one past the end — and reading through that index yields nulls rather than an error:

q)select sym, mlink.description, mlink.roundLot from t2
sym    description                                      roundLot
----------------------------------------------------------------
ASND   "Ascendis Pharma A/S American Depositary Shares" 100
TSLA   "Tesla, Inc. Common Stock"                       100
BSBK   "Bogota Financial Corp. Common Stock"            100
EMA    "Emera Incorporated"                             100
AGEN   "Agenus Inc."                                    100
NOSUCH ""

That is the trade-off against a foreign key: a link imposes no referential integrity, and will happily store an index that points nowhere. Nothing stops you writing an arbitrary row number into a link column, so the correctness of a link is yours to maintain.

To link on more than one column, search for the combinations rather than single values: flip both sides into lists of tuples and find one in the other.

trade records both the instrument and the venue, so a table of per-instrument, per-venue statistics is a natural parent. Derive one from quote:

q)venue:0!select avgSpread:avg ask-bid by sym,ex from quote
q)update vlink:`venue!(flip venue`sym`ex)?flip t`sym`ex from `t
`t
q)select sym, ex, vlink.avgSpread from t
sym  ex avgSpread
-----------------
ASND S  0.9968831
TSLA P  1.016075
BSBK Y  1.03875
EMA  D  0.998
AGEN A  1.011786

A row whose combination is absent from the parent gets an out-of-range index and reads back null, as in the simple case — so a compound link matches fewer rows than a link on either column alone.

Limits

A link cannot span partitions. The indexes in a link column are row numbers within one partition's parent, so for a date-partitioned database you cannot link across days. Create the link as each partition is written, and the problem does not arise.

The parent cannot be a partitioned table. A link whose domain is partitioned signals par — the parent has to be a single table, such as the splayed master above:

q)select trade.price from ([] id:1 2; trade:`trade!0 1)
'par
q)select master.description from ([] id:1 2; master:`master!0 1)
description
-------------
"Apple Inc."
"Agenus Inc."

A link column whose domain is a partitioned table requires the encompassing table to be partitioned too. Signalling par here dates from 4.1t 2022.04.15.

Grouping is cheaper than it was. Since 4.1t 2023.08.04 and 4.0 2023.08.11, references to linked columns under group by no longer remap the parent column for every group.

Linking across separate databases

For practical purposes only one on-disk database is memory-mapped to a process at a time, which makes analytics spanning two databases awkward. Aggregating over IPC works but does not scale to many days of data. On Unix-like systems the alternative is symbolic links plus link columns: the tables stay in their own databases, and one process reads both.

The outline is three steps:

  1. Build the partitioned databases, one table in each.
  2. For each date, map the rows of the base table to the rows of the remote table — typically with an as-of join on time and sym.
  3. Append a link column recording that mapping and write it down.

Use .Q.ens and .Q.dpfts rather than .Q.en and .Q.dpft: both take an explicit sym-table argument, which is what keeps the two databases' sym files from clashing.

First the two databases, db1 holding trade and db2 holding quote:

trade:([] time:`time$(); sym:`$(); price:`float$(); size:`int$())
quote:([] time:`time$(); sym:`$(); bid:`float$(); bsize:`int$(); ask:`float$(); asize:`int$())

n:10000
st:08:00:00.000
et:17:00:00.000
syms:`A`B`C`D

insert[`trade; (asc st+n?et-st; n?syms; n?100f; n?1000)]

/ ten quotes per trade
n*:10
insert[`quote; (asc st+n?et-st; n?syms; n?100f; n?1000; n?100f; n?1000)]

buildHDB:{[dir;dt;t] .Q.dpft[dir;dt;`sym;t];}
buildHDB[`:db1;;`trade] each .z.D-til 3
buildHDB[`:db2;;`quote] each .z.D-til 3

The first utility creates the symlink from the base database to the remote table, if it is not already there:

.lc.createSymLink:{[basePath;remotePath;rTab]
  (baseTablePath;remoteTablePath):{[path;rTab]
    raze system "realpath ",path,"/",rTab}[;rTab] each (basePath;remotePath);
  if[not(`$rTab) in key hsym `$basePath;
    system "ln -s ",remoteTablePath," ",baseTablePath]; }

The second joins the two tables for one date, builds the link, and writes the result with an independent sym file:

.lc.joinSaveTables:{[ajCols;basePath;dt;baseTable;remoteTable]
  remoteTable:`$remoteTable;
  baseTable:`$baseTable;

  / force-load the as-of join columns from the remote table
  remoteFileHandle:` sv (hsym `$string dt),remoteTable;

  / load the db to pick up the right sym file
  pwd:raze system "pwd";
  system "l ",basePath;

  remoteTable set select sym,time from (get remoteFileHandle);

  / re-apply the original attributes to the in-memory copy
  ![remoteTable;();0b;a[`c]!{(#;enlist x;y)} .'
    flip value a:exec a,c from meta get remoteFileHandle where c in ajCols];

  / join, then set the link column to where the tables map together
  baseTable set aj[
    ajCols;
    select from value baseTable;
    ?[value remoteTable; (); 0b; (ajCols!ajCols),(enlist `id)!enlist `i] ];
  update link:remoteTable!
    (exec i from select i from value remoteTable)?id from baseTable;

  / splay the base table with its own sym file `tsym
  system "cd ",pwd;
  .Q.dpfts[hsym `$basePath;dt;`sym;baseTable;`tsym]; }

Run them over the dates to link every partition:

basePath:"db1"
remotePath:"db2"
baseTable:"trade"
remoteTable:"quote"

{.lc.createSymLink[raze basePath,"/",string x; raze remotePath,"/",string x; remoteTable]} each .z.D-til 3
.lc.joinSaveTables[`sym`time; basePath;; baseTable; remoteTable] each asc .z.D-til 3

db1 now holds a trade table with a link into the quote table that physically lives in db2:

q)\l db1
q)tables[]
`quote`trade
q)meta trade
c    | t f     a
-----| ---------
date | d
sym  | s       p
time | t
price| f
size | i
id   | j
link | i quote

A single-table query then reaches both:

q)select size wavg price, bsize wavg bid, asize wavg ask
    by sym, 10 xbar time.minute
    from select time,sym,size,price,link.ask,link.asize,link.bid,link.bsize
         from trade where date=max date

Repeating that query is markedly cheaper than the first run, because the column files it touches are then in the operating system's page cache — the white paper reports the second run of its equivalent completing in a small fraction of the first. That is a property of reading the same files again, not of the link itself.

Next steps