cancel
Showing results for 
Search instead for 
Did you mean: 

loading table with foreign keys

User
Not applicable
Received: by 10.100.95.15 with SMTP id s15mr1681374anb.26.1249304565161; Mon, 03 Aug 2009 06:02:45 -0700 (PDT)Date: Mon, 3 Aug 2009 06:02:45 -0700 (PDT)X-IP: 125.17.110.216User-Agent: G2/1.0X-Google-Token: H9p8QQwAAAAqWRNxh1-Vl_Ie-jz7mgMrX-HTTP-Via: 1.1 ISAX-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)Message-ID: <68fdb34d-ded6-4548-b82c-4dc885961a76@s31g2000yqs.googlegroups.com>Subject: loading table with foreign keysFrom: bharani To: "Kdb+ Personal Developers" X-Google-Approved: simon.garland@gmail.com via web at 2009-08-03 14:35:33I am relative new to kdb+ and i am trying to load a table with foreignkey from a csv file.I have got two tables likep:([p:()] t:()) / parent tablea:([a:()] p:`p$()) / child table with p as foreign keyi can load the table p from a csv file with out any problem withCols: ("II";",") 0:`d:/p.dat`p insert flip Colsbut i am not sure how to do it for table a because it has the foreignkey p. From the documentation i understand it has to be of typeenumeration but not sure how to get it to enumerate when i am loadingfrom the csv file-Bharani
5 REPLIES 5

Aaron_Davies
New Contributor
X-Mailer: Apple Mail (2.935.3)On Aug 3, 2009, at 9:02 PM, bharani wrote:> I have got two tables like>> p:([p:()] t:()) / parent table>> a:([a:()] p:`p$()) / child table with p as foreign key>> i can load the table p from a csv file with out any problem with>> Cols: ("II";",") 0:`d:/p.dat> `p insert flip Cols>> but i am not sure how to do it for table a because it has the foreign> key p. From the documentation i understand it has to be of type> enumeration but not sure how to get it to enumerate when i am loading> from the csv filei think the keying happens automatically, given that it's in the schemacompare:q)p:([p:()] t:()) / parent tableq)a:([a:()] p:`p$()) / child table with p as foreign keyq)`a upsert flip("II";",")0:`a.dat'castie insert w/o corresponding parents failsq)`p upsert flip("II";",")0:`p.dat`pq)`a upsert flip("II";",")0:`a.dat`aq)exec p from a`p$1 2 3once parents exist, upsert succeeds, and column is an enumeration

User
Not applicable
User-Agent: G2/1.0X-Google-Token: mqQQTgwAAAAudDU7FvOwxbf2LF_WB24eX-HTTP-Via: 1.1 ISAX-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)Message-ID: Subject: Re: loading table with foreign keysFrom: bharani To: "Kdb+ Personal Developers" X-Google-Approved: charlie@kx.com via web at 2009-08-04 12:22:50Thanks for the quick response. But I am wondering why insert fails inthis scenario and upsert works`p upsert flip Cols / this works`a upsert flip Cols / this also worksbut`p insert flip Cols / this works`a insert flip Cols / but this does not. insert actually inserts thekey ( a in this case) but the enum column (p) is empty-BharaniOn Aug 3, 8:41�pm, Aaron Davies wrote:> On Aug 3, 2009, at 9:02 PM, bharani wrote:>> > I have got two tables like>> > p:([p:()] t:()) �/ parent table>> > a:([a:()] p:`p$()) �/ child table with p as foreign key>> > i can load the table p from a csv file with out any problem with>> > Cols: ("II";",") 0:`d:/p.dat> > `p insert flip Cols>> > but i am not sure how to do it for table a because it has the foreign> > key p. From the documentation i understand it has to be of type> > enumeration but not sure how to get it to enumerate when i am loading> > from the csv file>> i think the keying happens automatically, given that it's in the schema>> compare:>> q)p:([p:()] t:()) �/ parent table> q)a:([a:()] p:`p$()) �/ child table with p as foreign key> q)`a upsert flip("II";",")0:`a.dat> 'cast>> ie insert w/o corresponding parents fails>> q)`p upsert flip("II";",")0:`p.dat> `p> q)`a upsert flip("II";",")0:`a.dat> `a> q)exec p from a> `p$1 2 3>> once parents exist, upsert succeeds, and column is an enumeration

X-Mailer: Apple Mail (2.935.3)On Aug 4, 2009, at 8:10 PM, bharani wrote:> `p insert flip Cols / this works> `a insert flip Cols / but this does not. insert actually inserts the> key ( a in this case) but the enum column (p) is emptycan you post actual examples, including table data? insert and upsert have different semantics in many situations

User
Not applicable
I did some more tests and here is what it loos likedoing the following in q worksp:([p:()] t:())a:([a:()] p:`p$())Cols: ("II";",") 0:`d:/oc.dat`p upsert flip Cols`a upsert flip Colswhere d:/oc.dat is a file with contents3,34,4now quit and enter into a new q session and do the followingp:([p:()] t:())a:([a:()] p:`p$())Cols: ("II";",") 0:`d:/oc.dat`p insert flip Cols`a insert flip ColsI think the problem is because when i do `p insert flip Cols i getoutput like0 1i dont know what that means but typing p gives mep|t3|43|4which is wrong - it should have been3|34|4so i am wondering is "insert flip" a good idea or i should only use"upsert flip"-BharaniOn Aug 4, 6:24�pm, Aaron Davies wrote:> On Aug 4, 2009, at 8:10 PM, bharani wrote:>> > `p insert flip Cols �/ this works> > `a insert flip Cols �/ but this does not. insert actually inserts the> > key ( a in this case) but the enum column (p) �is empty>> can you post actual examples, including table data? insert and upsert �> have different semantics in many situations

X-Mailer: Apple Mail (2.935.3)> p:([p:()] t:())> a:([a:()] p:`p$())> Cols: ("II";",") 0:`d:/oc.dat> `p insert flip Cols> `a insert flip Cols>> I think the problem is because when i do `p insert flip Cols i get> output like> 0 1that's the indices of the new rows> i dont know what that means but typing p gives me> p|t> 3|4> 3|4>> which is wrong - it should have been>> 3|3> 4|4>> so i am wondering is "insert flip" a good idea or i should only use> "upsert flip"it really helps if you give an actual session transcriptin this case, the linesq)`a insert flip Cols'castare key--it indicates that what you're trying to put into a.p doesn't exist in p.pthings become clearer if you don't use a square table:q)p:([p:()] t:())q)a:([a:()] p:`p$())q)Cols: ("II";",") 0:`oc2.datq)`p insert flip Cols'lengthq)`p insert Cols0 1 2q)`a insert Cols0 1 2q)pp| t-| -3| 34| 45| 5q)aa| p-| -3| 34| 45| 5so yes, "insert flip" is wrong