Search code examples
kdbk

Enums for tables

I found no information about what the enum is over the table domain on https://code.kx.com/q/ref/enumerate/. But something interesting exists there: https://code.kx.com/q/kb/linking-columns. I tried those examples and found an enum structure that behaves in some situations like a normal enum, but has a strange behaviour in others.

q)kt:1!t:([]a:`a`b`c;b:10 20 30)
q)tt:([]k:`a`a`a`b;d:11 21 31 41)
q)show et1:`t!t[`a]?tt[`k]
`t!0 0 0 1
q)show et2:`kt$tt[`k]
`kt$`a`a`a`b
q)meta select k,d,et1,et2 from tt
c  | t f  a
---| ------
k  | s     
d  | j     
et1| j t   
et2| s kt  
q)select r1.a, r1.b, r2.a, r2.b from update r1:et1, r2:et2 from tt
a b  a1 b1
----------
a 10 a  10
a 10 a  10
a 10 a  10
b 20 b  20

From this perspective et1 and et2 both have similar behaviour. But if we check other enum properties, we see differences:

q)et2[0]
`kt$`a
q)et2[0]:`a
q)
q)et1[0]
`t!0
q)et1[0]:0 / neither works this
't
  [0]  et1[0]:0
             ^
q)et1[0]:(`a`b!(`a;10)) / nor that
't
  [0]  et1[0]:(`a`b!(`a;10))
             ^

The situation seems more weird if we build enums for just a keyed tables: see a difference for a table with one key column and for two:

q)kkt:2!t:([]a:`a`b`c;b:10 20 30;c:11 22 33)
q)kt:1!0!kkt
q)show ekkt:`kkt$((`a;10);(`b;20);(`b;20))
`kkt!0 1 1
q)show ekt:`kt$(`a`b`b)
`kt$`a`b`b

The same hardcoded (with !) enum notation for kkt.

So the question: what are they? - those enums with a familiar $ and with a hardcoded ! notaions for a table? Is it possible to apply enum-extend technique (?) for them and how? And is there any documentation for them?

Solution

  • What you're seeing is the difference between a simple foreign key and a linked column. As mentioned in the documentation, differences include:

    • a foreign key is specifically designed to link to the keys of a keyed table.
    • A foreign key does not allow the link if there's an "unknown key" that isn't one of the keys in the keyed table
    • linked columns can link to any arbitrary column (if even a value doesn't appear in the other table - thus it doesn't guarantee referential integrity)
    • linked columns are generally used for on-disk tables
    q)kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)tdetails2:([] eid:1003 1001 1002 1001 1002 1001 777;sc:126 36 92 39 98 42 7)
q)update linker:`kt!((0!kt)`eid)?eid from `tdetails2
`tdetails2
q)select linker.name from tdetails2
name
----------
Prefect
Dent
Beeblebrox
Dent
Beeblebrox
Dent

    The latter would not have been allowed for a simple foreign key.

    Also I don't know why you would want to modify /edit the values of an enumeration - don't do that!