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

Martin Fischer martin.fischer6 at web.de
Thu Aug 31 17:06:46 CEST 2023

> 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,


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...
Anyway. I changed the schema definition to:

CREATE TABLE "person" (
	"sex_id"	INTEGER NOT NULL REFERENCES "sex"("sex_id"),
	"given_name"	VARCHAR(80) NOT NULL,
	"sir_name"	VARCHAR(80) NOT NULL,
	"birthdate"	DATE

and addition of a new row now works, even if I do not provide a value
for the "person_id" column. As it should be...

Thank you

More information about the User mailing list