Subject Re: creating unique key : usingprimair key and foreign key From Ken Mayer <dbase@nospam.goldenstag.net> Date Sat, 6 Feb 2021 10:33:29 -0800 Newsgroups dbase.getting-started On 2/6/2021 8:58 AM, Akshat Kapoor wrote: > Good Evening Ken, > >>>>> In a multi-user system one can't guarantee that the last record in the >>>>> main table is the one you've just saved.  Another user may have >>>>> saved a >>>>> record a fraction of a second after you.  SQL RDBMs make provision for >>>>> a programmer to get the value of the primary key of a record just >>>>> saved. >>>>> dBASE doesn't. :-( >>>>> >>>>> The solution is not to use an autoinc field but rather use a separate >>>>> sequence generator.  Primary keys do not need to be in numerical order >>>>> in a table.  They just need to be unique.  If you want to list records >>>>> in the order in which they were saved add a timestamp.  Get a value >>>>> BEFORE saving the new parent record and use that.  There is code in >>>>> the >>>>> dUFLP (seqval.cc) to do this. >>> >>> When programming for network at times we do require primary key value >>> of the row we have just inserted. >>> >>> Autoinc will give each row a unique value but there is NO GUARANTEE >>> that the last row in the table is the row you inserted. It could have >>> been inserted by someone else also. >>> So how do you get the primary key value of the row just inserted. >>> >>> Mervyn is issuing a warning for that only. Because this bug in coding >>> will be damn difficult to debug. >> >> Even with a large number of users, the way autoincrement works is that >> the value is inserted when the save() method of the rowset is called. >> There is very little chance of duplicate values, because even if two >> people manage to save at the same time, there will be at least a tiny >> amount of difference, during which time the value is saved and the >> internal value is updated. I have not once heard of a case where an >> autoincrement value was an issue. > > I just tried to explain what Mervyn said, At times it is difficult to > obtain the value You inserted. > > We never said that there could be duplicates. > > If we are using beginappend() then we can obtain the value just inserted > but if we are using sql then there is no confirmation that the last row > was inserted by you. rowset.beginAppend() // add the data rowset.save() // now, search for unique values in the data, perhaps a combination of fields, like // first and last names, or something else that would make the record // unique, perhaps first and last names and zip code, street address, // whatever // at this point the value of the autoincrement is in the field, and you // can obtain it. I have had to do something along those lines in some of my own code on the web for applications when I needed the primary key right after the record was saved. Ken -- *Ken Mayer* Ken's dBASE Page: http://www.goldenstag.net/dbase The dUFLP: http://www.goldenstag.net/dbase/index.htm#duflp dBASE Books: http://www.goldenstag.net/dbase/Books/dBASEBooks.htm dBASE Tutorial: http://www.goldenstag.net/dbase/Tutorial/00_Preface.htm