SQLite Autoincrement
Hello, I have exported the SQL for a SQLite Database. In the tables, I have PRIMARY_KEY and AUTOINCREMENT.
CREATE TABLE CSS_AT_RULES ( id integer NOT NULL AUTOINCREMENT, css_version varchar(10) NOT NULL, syntax_id integer NOT NULL, CONSTRAINT CSS_AT_RULES_pk PRIMARY KEY (id) );
Using this format, SQLite is throwing an error due the AUTOINCREMENT is not declared as PRIMARY KEY yet (it is being done in the CONSTRAINT). I am using the java sqlite-jdbc-3.8.11.2.jar driver from org.xerial
SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (near "AUTOINCREMENT": syntax error) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "AUTOINCREMENT": syntax error)
A possible correct syntax (regarding to the SQLite documentation https://www.sqlite.org/autoinc.html ) for the previous example would be:
CREATE TABLE CSS_AT_RULES ( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, css_version varchar(10) NOT NULL, syntax_id integer NOT NULL );
Could you please confirm if I am right?
Regards,
Javier.
Svar
Hello,
Yes, it is a bug.
I think, the correct syntax is (as sqlite's faq mentions https://www.sqlite.org/faq.html#q1):
CREATE TABLE CSS_AT_RULES ( id integer NOT NULL, css_version varchar(10) NOT NULL, syntax_id integer NOT NULL, CONSTRAINT CSS_AT_RULES_pk PRIMARY KEY (id) );
If column has type 'integer' and 'primary key' then has also 'autoincrement'.
In this case, the fastes solution is to unset an autoincrement option.
I hope this helps.
Yes, I can confirm it.
I removed the AUTOINCREMENT of an integer "primary key" column and it worked as AUTOINCREMENT.
So, unsetting AUTOINCREMENT would solve the bug.
Thank you.
Kundesupport af UserEcho
Hello,
Yes, it is a bug.
I think, the correct syntax is (as sqlite's faq mentions https://www.sqlite.org/faq.html#q1):
If column has type 'integer' and 'primary key' then has also 'autoincrement'.
In this case, the fastes solution is to unset an autoincrement option.
I hope this helps.