0
Fixed

SQLite Autoincrement

Javier Tejedor 8 years ago updated by hepeyowa 5 years ago 3

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.

Answer

Answer
Under review

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.


Answer
Under review

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.



Fixed

This has already been fixed and is on production.