SQL error: near "AUTOINCREMENT": syntax error
Bigfootmech opened this issue · 7 comments
Hey, I could be totally wrong here.
New to this.
Trying to setup an "id" field (with datatype "int", primary key, autoincrement, not null, and unique
Except, if I make it "autoincrement" and "unique", Godot-SQLite fails with an SQL syntax error
eg
these work fine:
native_example["id"] = {"data_type":"int", "primary_key": true, "not_null": true, "auto_increment":true}
native_example["id"] = {"data_type":"int", "primary_key": true, "not_null": true, "unique": true}
this one will crash:
native_example["id"] = {"data_type":"int", "primary_key": true, "not_null": true, "auto_increment":true, "unique": true}
Running a "query" with a create table statement made by "DB Browser for SQLite"
CREATE TABLE "Food_Simple" (
"id" INTEGER NOT NULL UNIQUE,
"name" TEXT NOT NULL UNIQUE,
"calories" REAL NOT NULL,
PRIMARY KEY("id" AUTOINCREMENT)
);
also works just fine
So I'm assuming it's some internal logic to this extension? :S
Hello @Bigfootmech,
What is the SQL syntax error?
Good question. Not entirely sure. How do I get Godot to show me a bigger stacktrace / more explanation?
I turned up the verbosity on the database, it seems that this statement is the one that fails
CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,name text NOT NULL);
An online SQL validator also complains
https://www.eversql.com/sql-syntax-check-validator/
If I remove "Unique", the SQL statement ends up as:
CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name text NOT NULL);
which according to SQL checkers "shouldn't" work, but somehow does :S
I'm out of my depth here
Seems like this is an order problem:
Does not work:
CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,name text NOT NULL);
Works:
CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,name text NOT NULL);
I'll probably have to swap the order of AUTOINCREMENT and UNIQUE in the source code.
For now, as a work-around, you can do this:
db.query("CREATE TABLE IF NOT EXISTS Food_Simple (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,name text NOT NULL);")
(Instead of using the create_table convenience method)