Default Primary Key Integer?

[Richard_Summers]

Hi,
I was under the impression that I could set the initial Integer Primary Key to 75 by using the following code:

addressDB.SQLExecute("CREATE TABLE addressbook(NRef INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 75, name TEXT)")

However - when I add an entry into the database and then look at the database file - the initial entry has a Primary Key of 1
What am I missing?

Thanks.

[Christian_Schmitz]

you can’t set default here.

The current value of auto increment is stored differently.

[Richard_Summers]

Thanks Christian.
Do you know how I set the very first Primary Key to my desired value?

Thanks.

[Christian_Schmitz]

how about you tell use which database type you target? SQLite?

[Christian_Schmitz]

for SQLite read here:
https://www.sqlite.org/autoinc.html

and I found this:

SQLite keeps track of the largest ROWID that a table has ever held using an internal table named “sqlite_sequence”. The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. 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.

[Richard_Summers]

Thanks Christian - yes, SQLite.
I will take a look now.

Thank you.

[Peter_Fargo]

sqlite_sequence is the table where these are held in SQLite

column name contains table names
column seq contains the last auto increment value used

[Norman_P]

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 ?)

[Russ_Lunn]

insert 75 records, then delete them?

[Richard_Summers]

Thanks guys - I will look into all these recommendations.
Much appreciated.

[Simon_Berridge]

@194300:@Russ Lunn

insert 75 records, then delete them?

Do as Russ says. After the Create Table instruction run 74 insert instructions. Then delete from Table. That will leave an empty table but the auto increment will set the next insert primary key to 7.

But, I have to ask, why do you need the first primary key to be 75?

Simon.

[Jean-Yves_Pochez]

@194341:@Simon Berridge

But, I have to ask, why do you need the first primary key to be 75?

++++1.

[Christian_Schmitz]

he should never need it.

[Paul_Lefebvre]

@194341:@Simon Berridge

But, I have to ask, why do you need the first primary key to be 75?

I wonder too. The primary key should not hold meaning. If you need a column that contains a 75, then create a different column and set it to 75 like you want.

[Richard_Summers]

I was asking on someone else’s behalf. I too thought it was unnecessary, but I thought I would try to find out for them anyway.

[Rick_Araujo]

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;"
db.SQLExecute(sql)

End Sub

forceSQLiteSeq(myDB, "myTableName", 75)

[Paul_Raulerson1 (Paul Raulerson)]

@194474:@Paul Lefebvre

I wonder too. The primary key should not hold meaning. If you need a column that contains a 75, then create a different column and set it to 75 like you want.

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.

-Paul

[Paul_Lefebvre]

Yeah, I was referring to auto-generated primary key. Left out a few words, I guess.

Of course, there’s still no way I would use a policy number as a primary key, but that’s me.

[Paul_Raulerson1 (Paul Raulerson)]

(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…

[Norman_P]

@194722:@Paul Raulerson

(grin) What would you use if not a policy number? In most companies, a policy number is unique.

Right up until one company acquires another & they have duplicates - been there done that had to fix that
It sucks
Primary keys should not have any meaning
Other candidate keys may but they are not primary keys