Invalid SQL generated for SQLite primary key w/AUTOINCREMENT

[GoogleCodeExporter]

The designer generates invalid SQL for SQLite for tables containing a
PRIMARY KEY constraint with AUTOINCREMENT. SQLite permits the AUTOINCREMENT
keyword only as a column constraint, and then it must follow "PRIMARY KEY".
http://www.sqlite.org/lang_createtable.html

Pasting the attached XML into the designer generates a table which gives
this SQL which gives a syntax error in SQLite:
CREATE TABLE foo (
id INTEGER NOT NULL AUTOINCREMENT ,
PRIMARY KEY (id)
);

It seems like this is the only "PRIMARY KEY AUTOINCREMENT" syntax that will
work:
CREATE TABLE foo (
id INTEGER PRIMARY KEY AUTOINCREMENT
);

The documentation says the SQL below is a syntax error, and experimentation
confirms that:
CREATE TABLE foo (
id INTEGER,
PRIMARY KEY AUTOINCREMENT (id) 
);

This is also a syntax error:
CREATE TABLE foo (
id INTEGER,
PRIMARY KEY  (id) AUTOINCREMENT
);

It's a great tool; thanks!

Philip Semanchuk

Original issue reported on code.google.com by NikitaTh...@gmail.com on 26 Feb 2010 at 9:28

Attachments:

• pk_error_demo.xml

[GoogleCodeExporter]

Original comment by ondrej.zara on 27 Feb 2010 at 12:27

• Changed state: Accepted
• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

My example of working syntax was a little misleading. I should clarify that the
PRIMARY KEY syntax also accepts NOT NULL. These are valid, in addition to the 
syntax
shown above:

CREATE TABLE foo (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL 
);

CREATE TABLE foo (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

Original comment by NikitaTh...@gmail.com on 27 Feb 2010 at 2:39

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Fixed in r79. Please test and let me know if further adjustments are necessary.

Original comment by ondrej.zara on 28 Feb 2010 at 9:03

• Changed state: Fixed
• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Works for me. Thanks for the quick fix.

Original comment by NikitaTh...@gmail.com on 1 Mar 2010 at 2:08

• Added labels: ****
• Removed labels: ****