# 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](foreign-keys.md) 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](overview.md) for how the two mechanisms compare and when to choose which.

## The sample data

The examples use the [Datagen module](../../../modules/datagen/overview.md), asking for a **splayed** `master` table so that it can serve as the parent of a link on disk:
```q
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:
```treeview
mdb
├── 2026.04.06
│   └── trade
├── 2026.04.07
│   └── trade
├── exnames
├── master
│   ├── .d
│   ├── cusip
│   ├── description
│   ├── description#
│   ├── ex
│   ├── issueprice
│   ├── roundLot
│   ├── securityType
│   ├── sym
│   └── testFlag
└── sym
```

## Create a link column

A link column holds, for each child row, the **row number** of the matching parent row. Building one is therefore a [find](../../../ref/find.md) of the child's values in the parent's column, wrapped in the [Enumeration operator `!`](../../../ref/enumeration.md) 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
q)m:0!master
q)update mlink:`m!(m`sym)?sym from `t
`t
```

[`meta`](../../../ref/meta.md) reports it in the `f` column, exactly as it reports a foreign key, and the column type is a long:
```q
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
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
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
```

### On disk, adding a link to an existing database

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](../splayed-tables.md), which is what tells q the column exists:
```q
q)\l mdb
q)msym:master`sym
q)addLink:{[par]
    d:hsym `$"./",(string par),"/trade";
    tsym:get .Q.dd[d;`sym];
    .Q.dd[d;`mlink] set `master!msym?tsym;
    .[.Q.dd[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
```

Reload the database for the new column to appear:
```q
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
```

> **Note — 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](#limits).
>
## Query across a link

Dot notation reads the parent's columns through the link, with no join in the query and none performed:
```q
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
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](../../../ref/find.md) 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
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 happily stores 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.

## Compound links

To link on more than one column, search for the combinations rather than single values: [`flip`](../../../ref/flip.md) 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
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 doesn't 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
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. Signaling `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.

## Link across separate databases

For practical purposes, a process memory-maps only one on-disk database at a time, which makes analytics spanning two databases awkward. Aggregating over interprocess communication (IPC) works but doesn't 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](../../../ref/aj.md) on `time` and `sym`.
3. Append a link column recording that mapping and write it down.

Use [`.Q.ens`](../../../ref/dotq.md#ens-enumerate-against-domain) and [`.Q.dpfts`](../../../ref/dotq.md#dpfts-save-table-with-symtable) 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`:
```q
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:
```q
.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:
```q
.lc.joinSaveTables:{[ajCols;basePath;dt;baseTable;remoteTable]
  remoteTable:`$remoteTable;
  baseTable:`$baseTable;

  / force-load the as-of join columns from the remote table
  remoteFileHandle:.Q.dd[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:
```q
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
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
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](../loading-db.md#pre-fetching-with-23) — the [white paper](https://code.kx.com/q/wp/foreign-keys/) 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

- Compare with the in-memory equivalent in [foreign keys](foreign-keys.md).
- See how the `.d` file defines a splayed table's columns in [splayed tables](../splayed-tables.md).
- Understand why links cannot cross partitions in [partitioned tables](../partition.md).
