How do I add in the existing Segmented Table foreign key?

Community Support
1

Hi All,

How do I add in the existing Segmented Table foreign key?

Can you help me?

Thanks,

Sky

2

Hi Sky,

To make a row in a table a foreign key, it just needs to be cast to the type of the other table. The
the type of the foreign key column should be compatible with the type of the primary key column in
the other table! You can use the same update syntax to add a new column. So, assuming you have
another table called stock and you want to make sym your foreign key that references it:

    t:update sym:`stock$sym select from tbl where date = 2015.01.27

Once you have updated that part of the table, you should be able to write it back down to disk
using set:

    ($(string .Q.par[:.;2015.01.27;tbl]),"/") set .Q.en[:.;t]

You can then use .Q.pf and .Q.par to iterate through all of the paths (assuming that a global
with the same name hasn’t been created elsewhere and that the partitions were allocated with
.Q.par).

A longer example of making the sym column a foreign key:

    q) s:exec sym from select distinct sym from quote
    q) count s
    15
    q) s
    sym$AAPLAIGAMDDELLDOWGOOGHPQIBMINTCMSFTORCLPEPPRUSBUXTXN     q) stock:([sym:s]industry:count[s]?energyretailfinance;currency:count[s]?USDEURGBP)     q)     q) tmp:select from quote where date=last date     q) count tmp     75296     q)     q) tmp:update sym:stock$sym from tmp
    q) meta tmp
    c    | t f     a
    -----| ---------
    date | d
    sym  | s stock
    time | t
    bid  | f
    ask  | f
    bsize| j
    asize| j
    mode | c
    ex   | c

The database has been generated using the buildhdb.q script from http://code.kx.com/svn/cookbook_code/start/buildhdb.q
if you want to try it yourself.

Thanks

–
Mark Rooney
Financial Software Developer
AQUAQ Analytics

3

Hi Mark,
Thank you very much.Your answer is very useful to me.

Best regards,

Sky


? 2015?1?27??? UTC+8??2:50:26?huangyu…@gmail.com???

Hi All,

How do I add in the existing Segmented Table foreign key?

Can you help me?

Thanks,

Sky