[Gambas-user] DataBrowser: insert new record with autoincrement PK does not work

[Benoît Minisini]

Le 31/08/2023 à 17:06, Martin Fischer a écrit :
>>
>> It's because you didn't create your database from Gambas.
>>
>> In other words, at the moment, the only syntax that allows the
>> 'gb.db.sqlite3' driver to detect auto increment primary key fields is:
>>
>> CREATE TABLE "person" (
>>       "person_id"    INTEGER AUTOINCREMENT,
>>       "sex_id"    INTEGER NOT NULL,
>>       "given_name"    VARCHAR(80) NOT NULL,
>>       "sir_name"    VARCHAR(80) NOT NULL,
>>       "birthdate"    DATE,
>>       PRIMARY KEY("person_id"),
>>       FOREIGN KEY("sex_id") REFERENCES "sex"("sex_id")
>> );
>>
>> I think the "FOREIGN KEY..." part is useless in sqlite, but I may be 
>> wrong.
>>
>> sqlite stores table schema as a string (the string used for creation the
>> table), not as a bnary structure.
>>
>> So analyzing it is difficult, as it depends how you write it, and SQL
>> syntax is horrible and not really standard.
>>
>> Regards,
>>
> 
> Benoit,
> 
> you are right, I created the schema with an external DB Browser.
> Incredible that the schema definition variant makes a difference here.
> After all the DB schema metadata should look identical for all possible
> syntax variants...

Indeed, but alas sqlite does not provide its internal DB schema 
metadata. Only the text of the table creation request, which may vary a 
lot for the same schema.

-- 
Benoît Minisini.