CODEWITHSUNDEEP
databaseforeign-keyskdb+q-lang
Martin Mlostek
Martin Mlostek

Reputation: 2960

KDB+ / Q table creation with foreign key

My question is about creating a table with q and using foreign keys. I know how to do it the following way

q)T1:([id:1 2 3 4 5]d1:"acbde")
q)T2:([id:1 2 3 4 5]f1:`T1$2 2 2 4 4)

But now lets say I want to create the table with the ! operator flipping a dictionary this way

q)T3:1!flip ((`id`f1 )!((1 2 3 4 5);(2 2 2 4 4)))

How can I set a foreign key to table T1s primary key with this way of creating a table.

Update

Well, I thought my upper example would be sufficient for myself to solve my actual problem, but unfortunately its not.

What if I have this lists of lists layout A and B

q)A:enlist 1 2 3 4 5
q)B:(enlist "abcde"), (enlist `v`w`x`y`z)
q)flip (`id`v1`v2)!(B,A)

How can I make the list A as foreign key to table T1?

Update 2

And how would I implement it if I have A coming from somewhere, not initializing it myself. Do I have to make a copy from the list?

Upvotes: 2

Views: 881

Answers (1)

jgleeson
jgleeson

Reputation: 955

You can use the same syntax on the column values list:

q)T3:1!flip ((`id`f1 )!((1 2 3 4 5);(`T1$2 2 2 4 4)))
q)T3~T2
1b

Update:

Again for this case we can use the same syntax on the list -

q)A:enlist`T1$1 2 3 4 5
q)meta flip (`id`v1`v2)!(B,A)
c | t f  a
--| ------
id| c
v1| s
v2| j T1

Update2:

Same syntax applied to the variable name:

q)A:1 2 3 4 5
q)meta flip (`id`v1`v2)!(B,enlist`T1$A)
c | t f  a
--| ------
id| c
v1| s
v2| j T1

Upvotes: 4

Related Questions