Primary key generation for KDB table

Question

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.

Answer 1

KDB already has one for in-memory tables - the i column. It's basically the 0-based index of each row.

Answer 2

Virtual column 'i' is just a index to columns. We can't use it as primary key in all the cases. For ex. if we delete something from table like:

t:([] id:1 2 3)

select from t where i=1

    output--> id:2

Let's delete a row from t:

t:delete from t where i=1

select from t where i=1

    output--> id:3

It gives you row at that index and not the row corresponding to some column value(primary column in this case). So for such kind of operations, it can't be used as primary key.

Answer 3

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