0
Fixed

SQLite Autoincrement

Javier Tejedor 4 year бұрын updated by hepeyowa 4 ай бұрын 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.