The only downside is the admonition But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.
without much in the way of saying what IS safe (ie updating the value ?)
Well, I don’t know why he wants that, but here is it. Use it wisely or you can crash your system overlapping already used numbers.
Sub forceSQLiteSeq(db As SQLiteDatabase, table As String, seqInit As Integer = 1)
seqInit = seqInit - 1
Dim sql As String = "BEGIN TRANSACTION; UPDATE sqlite_sequence SET seq = "+Str(seqInit)+_
" WHERE name = '"+table+"'; INSERT INTO sqlite_sequence (name,seq) SELECT '"+table+"', "+Str(seqInit)+_
" WHERE NOT EXISTS (SELECT changes() AS updated FROM sqlite_sequence WHERE updated <> 0); COMMIT;"
forceSQLiteSeq(myDB, "myTableName", 75)
That’s a pretty general statement, no? If you qualify that with a “a synthesized key should not hold meaning” then yeah, I can see where you are coming from. But primary keys often hold meaning and are quite valuable that way. They just have to be unique.
PolicyCompany-Policynumber for example, can be a really good primary key, and hold a lot of meaning within an application.
Admittedly, we are talking about a plain old integer key here, but still.
(grin) What would you use if not a policy number? In most companies, a policy number is unique. Depending upon the company, they usually do hold quite a bit of information, including things like the policy company, location, policy and/or payor group, geographic area, etc. We tend to use them in cases where we do optimized queries using “like” or other forms of short key reads. Real easy, fast, and reliable.
They also end to survive being normalized quite well, and make great foreign keys. That’s just my experience though. I really do not like to create synthetic keys if I don’t have to - just adds to the bytes to be backed up, and they are truly meaningless. Sometimes of course, they are just the ticket.
Never really looked at from your point of view though, and I guess, given the scale of applications using say, SQLlite, it probably doesn’t really make any difference size wise. Have to think about what you are saying there…
Right up until one company acquires another & they have duplicates - been there done that had to fix that
Primary keys should not have any meaning
Other candidate keys may but they are not primary keys