how to make a primary key / foreign key on 2 unkeyed tables ?

Community Support
1

  1. how to make a primary key / foreign key on 2 unkeyed tables ?

  2. Same as #1, except it has to be a compound key

2

Primary Key:

PTab:( qty:int$(); price:float$());

1!`tab                      — for primary key, qty is PKey

Foreign key..

FTab:( qty1:PTab$qty; b:int$())   /// Hete qty1 is foreign key. 

For foreign/compound key, please read useful topics…

https://qkdbdb.wordpress.com/2016/01/17/foreign-key-and-links-in-kdb/

http://code.kx.com/mkdocs/wp/the_application_of_foreign_keys_and_linked_columns_in_kdb.pdf

3

A foreign key requires one table to be keyed. When the case arises that neither tables can be keyed (if they both need to be splayed) then you can make use of linked columns.

Case 1: One linked column

q)n:10

q)t1:(sym:n?JPMGEBP;ex:n?TSETOKLON;size:n?10;px:n?10f)

q)t2:(sym:n?JPMGEBP;ex:n?TSETOKLON;bsize:n?10;asize:n?10;px:n?10f

q)update linkt2:t2!t2.sym?sym from t1 //this creates an index over the column you wish to link. (!) is used to make connection

`t1

q)t1

sym ex size px linkt2

-----------------------------

GE LON 9 1.958467 1

GE TOK 9 5.615261 1

JPM TOK 2 0.7043811 0

JPM TOK 5 2.124007 0

JPM LON 4 7.77882 0

BP TSE 2 4.844727 4

BP LON 5 6.827999 4

JPM LON 8 1.53227 0

GE TOK 7 5.350923 1

JPM LON 9 4.57328 0


q)select sym,ex,size,linkt2.asize,linkt2.bsize from t1 //dot notation used to create the ‘virtual column’

sym ex size asize bsize

------------------------

GE LON 9 4 4

GE TOK 9 4 4

JPM TOK 2 3 3

JPM TOK 5 3 3

JPM LON 4 3 3

BP TSE 2 8 4

BP LON 5 8 4

JPM LON 8 3 3

GE TOK 7 4 4

JPM LON 9 3 3


q):/db/t1/ set .Q.en[:/db;t1] //can be splayed

`:/db/t1/


q):/db/t2/ set .Q.en[:/db;t2]

`:/db/t2/

Case 2: Compound linked columns


q)n:10

q)t1:(sym:n?JPMGEBP;ex:n?TSETOKLON;size:n?10;px:n?10f)

q)t2:(sym:n?JPMGEBP;ex:n?TSETOKLON;bsize:n?10;asize:n?10;px:n?10f)

q)

q)update linkt2:t2!(t2.sym,'t2.ex)?(t1.sym,'t1.ex)from t1 //joins each sym and ex and finds where the pairs appear in t1

`t1

q)t1

sym ex size px linkt2

----------------------------

GE TSE 4 3.927524 6

BP TOK 6 5.170911 0

JPM LON 6 5.159796 3

JPM TOK 1 4.066642 10

BP LON 8 1.780839 7

JPM TOK 5 3.017723 10

GE TSE 4 7.85033 6

JPM TSE 9 5.347096 2

JPM TOK 2 7.111716 10

JPM LON 7 4.11597 3

q

q)

q)select sym,ex,size,linkt2.bsize,linkt2.asize from t1

sym ex size bsize asize

------------------------

GE TSE 4 8 2

BP TOK 6 8 4

JPM LON 6 8 3

JPM TOK 1

BP LON 8 6 1

JPM TOK 5

GE TSE 4 8 2

JPM TSE 9 5 3

JPM TOK 2

JPM LON 7 8 3


q):/db/t1/ set .Q.en[:/db;t1] //again can be splayed

`:/db/t1/

q):/db/t2/ set .Q.en[:/db;t2]

`:/db/t2/

q)\


q)\l /db //load in the db

q)t1

sym ex size px linkt2

----------------------------

GE TSE 4 3.927524 6

BP TOK 6 5.170911 0

JPM LON 6 5.159796 3

JPM TOK 1 4.066642 10

BP LON 8 1.780839 7

JPM TOK 5 3.017723 10

GE TSE 4 7.85033 6

JPM TSE 9 5.347096 2

JPM TOK 2 7.111716 10

JPM LON 7 4.11597 3

q)t2

sym ex bsize asize px

-----------------------------

BP TOK 8 4 0.1392076

BP TSE 0 1 7.148779

JPM TSE 5 3 1.946509

JPM LON 8 3 0.9059026

GE LON 5 7 6.203014

BP TOK 2 8 9.326316

GE TSE 8 2 2.747066

BP LON 6 1 0.5752516

GE TOK 9 4 2.560658

GE TOK 0 2 2.310108

q)select sym,ex,size,linkt2.bsize,linkt2.asize from t1

sym ex size bsize asize

------------------------

GE TSE 4 8 2

BP TOK 6 8 4

JPM LON 6 8 3

JPM TOK 1

BP LON 8 6 1

JPM TOK 5

GE TSE 4 8 2

JPM TSE 9 5 3

JPM TOK 2

JPM LON 7 8 3

4

You did not answer my first question. I need to make a pk/fk reln on unkeyed tables . I do not want to make an pk.

5

q)n:10

q)t1:(sym:JPMGEBP;ex:TSETOKLON)


q)t1

sym ex
-------
JPM TSE
GE TOK
BP LON

q)`sym xkey `t1
`t1

q)t1
sym| ex
---| ---
JPM| TSE
GE | TOK
BP | LON

q)t2:(sym:n?JPMGE`BP;bsize:n?10;asize:n?10;px:n?10f)


q)t2

sym bsize asize px

--------------------------

GE 7 4 9.591177

BP 0 2 4.121866

JPM 2 2 6.33041

BP 1 7 5.752693

JPM 8 2 0.09011743

JPM 1 0 1.448057

JPM 0 5 7.706132

JPM 7 7 3.696114

GE 3 5 1.175355

BP 3 6 9.234386


q)update sym:t1$sym from t2


q)select sym,bsize,asize,px,sym.ex from t2

sym bsize asize px ex
------------------------------
GE 7 4 9.591177 TOK
BP 0 2 4.121866 LON
JPM 2 2 6.33041 TSE
BP 1 7 5.752693 LON
JPM 8 2 0.09011743 TSE
JPM 1 0 1.448057 TSE
JPM 0 5 7.706132 TSE
JPM 7 7 3.696114 TSE
GE 3 5 1.175355 TOK
BP 3 6 9.234386 LON