Search code examples
kdb

Primary key generation for KDB table

Is there a way to specify a primary key column in KDB+ which will auto-increment, i.e. like in the traditional RMDS (say, Oracle's Sequence)? - don't need to insert in this column, it will be auto-populated with every new record.

Solution

  • Tables will automatically have a hidden (virtual) incrementing column called "i". 

    q)tab:([]col1:`a`b`c;col2:1 2 3)

q)select i,col1,col2 from tab
x col1 col2
-----------
0 a    1
1 b    2
2 c    3
q)
q)exec i from tab where col1=`b
,1

    If you needed to join on this you could so something like:

    q)tab2:([] index:2 3;col3:"yz")
q)
q)tab2
index col3
----------
2     y
3     z

q)(select index:i,col1,col2 from tab) lj 1!tab2
index col1 col2 col3
--------------------
0     a    1
1     b    2
2     c    3    y

    Some useful info here http://code.kx.com/q4m3/8_Tables/

    Edit - "i" is not a primary key but can be manipulated accordingly