SQLite Forum

Best way to handle unique column needs
I’m more informed now.

Some answers refer me back to the trick of having your primary key be an integer, and then it will reflect the rowed. However in my attempts I have found that it doesn’t return any number. When I save a record then fetch it back that field is zero. That’s my first problem. The main trick used for this purpose doesn’t seem to work. I realize that it should, but I find I only get zeros. That’s problem one. Which leads to my main problem still needing resolution… that is I need for new records a number to be assigned in the old column which contains 4000 records that already have a number assigned, which may be used for stored connections with other tables. So I want to preserve the old, and have new records get a new number. Higher than the last number used. 

To achieve this the one poster Larry Brasfield appears to be most on top of it. However, he says some things that confuse me. He says to recreate my tables and use MUMBLEID AND AUTOINCREMENT. 

My first question is what does MUMBLEID do or mean? 

Then I’m not clear.. on new records, do I have to pack the unique field myself, or will it get packed automatically?  One line refers to me fetching the last rowid and makes it seem like I have to do that to pack the field with that data.. which I can do, but I thought auto increment would do it for me. Or do you mean I have to fetch it only because that is my unique number and it will be inserted but if I want it sooner than a new fetch I need to fetch it after an insert.

My users are only single user always.. so fetching the last rowid inserted does make sense in my case. 

So I’m still not clear on if a number will be inserted for me, into that field, such that if I fetch the record back after an insert, that field will have a number in it. That is my aim. That when I save a NEW record a number will be put into that column such that when I fetch the record back it will have a  number in that column that is unique. 

Without full knowledge I got the FEELING that Larry;s answer was the closest… that mumbleID and auto increment are the answer. 

If however just having an INTEGER PRIMARY KEY field, which I already have, would do the trick, yes that column I’m interested in is already the Integer Primary Key.. if it would pack itself with a unique value that respects the other values already in that column in that table, then I would be set, but I just get back zeros in that column when I fetch after an insert. 

So there’s my need, unique on top of data already there, verse the problem of the primary key that is allready there not returning a value, but that system may not respect the data I already have in many records within the tables in question. 

I hope that clarifies my needs and situation. 

Thank you all for responding, I really appreciate it. 

Das Goravani

On May 5, 2020, at 5:09 PM, Larry Brasfield <noreplyf16e6c3f5@sqlite.org> wrote:

Forum post by LarryBrasfield on 2020-05-06 00:09:12

Your problem seems to be easily solved.

For your new tables, be sure to declare the unique integer value column as: " mumbleId INTEGER PRIMARY KEY AUTOINCREMENT, " with mumbleId chosen more intelligently.  This will be required to be unique as a consequence of being the primary key, and, per [this Rowid Tables](https://sqlite.org/rowidtable.html) documentation, will be an alias for the underlying rowid. (You may not care about this, but it improves efficiency somewhat.) Then, simply insert your values, specifying the mumbleId column values as the same unique numeric integer values from your old DB.  The autoincrement tracking will take care of tracking the highest value inserted so that, if ever an insert is done without specifying the mumbleId value, it can be generated. That would be the only circumstance where you would need to "fetch the last rowid after an insert."

If this does not work for some reason, or does not solve your issue, please show what goes wrong and be more specific about what shortcoming you perceive. (I had a difficult time understanding what problem you actually encountered.) It would help to show DDL and queries, together with results that seem awry.

One issue that was unclear in your post: We are discussing a primary key, right? And it is already referenced from other tables, I would expect. So you have an interest in preserving those linkages, right?