# How to work with keyed tables

_This page introduces keyed tables in q and shows how to create and work with them._

A keyed table is not a table but a dictionary (type `99h`), where:

- Its key is a table of one or more key columns.
- Its value is a table of the non-key columns.

In table notation, write the key fields inside the square brackets. A key is a unique identifier, similar to a primary key in a traditional SQL database.

> **Note — Key uniqueness**
>
>
> When you construct a table key, make sure its items are unique. To protect performance, KDB-X does not check that key items are unique.
>
> There is no use case for duplicate key items, as duplicates make operation results unpredictable.
>
Example:
```q
q)show market:([name:`symbol$()] address:())
name| address
----| -------
```

You can also add a key to an existing table using the [`xkey`](../../../ref/keys.md#xkey) keyword or the bang operator `!`. See [Convert a table to a keyed table](#convert-a-table-to-a-keyed-table) for how to choose between them:
```q
q)show t:([]sym:`MSFT`AAPL`IBM;px:98 42 126)
sym  px
--------
MSFT 98
AAPL 42
IBM  126

q)show kt:`sym xkey t           / key table on the sym column
sym | px
----| ---
MSFT| 98
AAPL| 42
IBM | 126

q)key kt
sym
----
MSFT
AAPL
IBM

q)value kt
px
---
98
42
126

q)keys kt
,`sym

q)1!t                           / key table on 1st column
sym | px
----| ---
MSFT| 98
AAPL| 42
IBM | 126

q)0!(1!t)                       / unkey table
sym  px
--------
MSFT 98
AAPL 42
IBM  126
```

## Convert a table to a keyed table

There are two ways to turn a table into a keyed table:

- [`xkey`](../../../ref/keys.md#xkey) takes the names of the key columns on the left: `` `sym xkey t``.
- The [enkey](../../../ref/enkey.md) operator `!` takes the number of key columns on the left: `1!t`. The first _n_ columns of the table become the key.

Both return a new keyed table and leave the original unchanged. They give the same result when the key columns are the leading columns of the table:
```q
q)show t:([] sym:`MSFT`AAPL`IBM; ex:`N`Q`N; px:98 42 126)
sym  ex px
-----------
MSFT N  98
AAPL Q  42
IBM  N  126
q)`sym xkey t
sym | ex px
----| ------
MSFT| N  98
AAPL| Q  42
IBM | N  126
q)(`sym xkey t)~1!t
1b
```

To use more than one key column, pass a list of names to `xkey`, or the number of columns to `!`:
```q
q)`sym`ex xkey t
sym  ex| px
-------| ---
MSFT N | 98
AAPL Q | 42
IBM  N | 126
q)(`sym`ex xkey t)~2!t
1b
```

### Choose between `xkey` and `!`

Use `xkey` in production code. It names the key columns, so:

- **It is explicit.** The code says which columns form the key and does not depend on the column order. If another change to the table, such as a new schema or a different `select` column list, reorders the columns, `xkey` still keys the same columns. `!` silently keys the wrong ones.
- **It is more readable.** A reader sees which columns are keyed without checking the table definition.

The `!` form is handy in ad hoc queries. It needs less typing, and it applies to any table, because you don't need to know the column names. That makes it a good fit for the q session and for short expressions such as `1!select sum size by sym from trade`. Switch to `xkey` once the code goes into a script.

To remove the key, use `0!`, as shown above.

## Foreign keys

A foreign key in SQL is a column in one table whose values are members of a primary key column in another table. Foreign keys are the mechanism for establishing relations between tables.

Foreign keys in SQL provide referential integrity: an attempt to insert a foreign key value that is not in the primary key fails. This is also true in q.

You can use only the primary key of a keyed table as a foreign key, but there are other ways to link table columns:

- [Foreign keys](../../../how_to/interact_with_databases/relationships/foreign-keys.md)
- [Linking columns](../../../how_to/interact_with_databases/relationships/linking-columns.md)

## Indexing a keyed table

There are two ways to index a keyed table.

First, index with a single row from the key, which returns a dictionary.
```q
q)/ keyed table with single key
q)show kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
eid | name       iq
----| --------------
1001| Dent       98
1002| Beeblebrox 42
1003| Prefect    126
q)flip {x cols x} key kt        / key values of the keyed table
1001
1002
1003

q)kt 1003
name| `Prefect
iq  | 126

q)/ keyed table with two keys
q)show kt:([eid:1001 1002 1003;name:`Dent`Beeblebrox`Prefect]; iq:98 42 126; shortName:`DT`BX`PT)
eid  name      | iq  shortName
---------------| -------------
1001 Dent      | 98  DT
1002 Beeblebrox| 42  BX
1003 Prefect   | 126 PT

q)flip {x cols x} key kt
1001 `Dent
1002 `Beeblebrox
1003 `Prefect

q)kt (1003;`Prefect)
iq       | 126
shortName| `PT
```

Second, index with a sublist from the key, which returns a list of dictionaries — that is, a table.
```q
q)kt ([]eid:1001 1002;name:`Dent`Beeblebrox)
iq shortName
------------
98 DT
42 BX
```

## Upserting records

[`upsert`](../../../ref/upsert.md) takes the same syntax as [`insert`](tables.md#appending-records).

For an unkeyed table, `upsert` is equivalent to `insert`.

For a keyed table, it is an `update` if the key exists in the table, or an `insert` otherwise.
```q
q)/ unkeyed table
q)show trade:([]stock:`ibm`bac`usb;price:121.3 5.76 8.19;amt:1000 500 800;time:09:03:06.000 09:03:23.000 09:04:01.000)
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
q)`trade insert (`ibm; 645.9; 200; 09:05:00.000)
,3
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
q)`trade upsert (`ibm; 645.9; 200; 09:05:00.000)
`trade
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000

q)/ keyed table
q)show table:([stock:`intel`ibm] price:(123.2 120.4 131; 111.2 140.3 160.7))
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm  | 111.2 140.3 160.7

q)`table upsert ([stock:enlist `aapl] price:enlist 140.5 160.8 180.9)   / key absent, so insert
`table
q)table
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm  | 111.2 140.3 160.7
aapl | 140.5 160.8 180.9

q)`table upsert ([stock:enlist `ibm] price:enlist 11.2 14.3 16.7)       / key present, so update
`table
q)table
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm  | 11.2  14.3  16.7
aapl | 140.5 160.8 180.9
```

An alternative syntax for `upsert` is to use the `,:` operator.

Example:
```q
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000

q)trade ,: (`aapl; 122.5; 50; 09:04:59.000)
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000
aapl  122.5 50   09:04:59.000
```

Like `insert`, `upsert` can also take a table as an argument:
```q
q)trade ,: ([] stock:`msft`msft; price:7.6 4.9; amt:300 700; time:09:06:00.000 09:07:59.000)
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000
aapl  122.5 50   09:04:59.000
msft  7.6   300  09:06:00.000
msft  4.9   700  09:07:59.000
```
