SQLite: Invalid SQL generated for multi-column primary keys

[GoogleCodeExporter]

When I create a table with a multi-column (composite) primary key, WWW SQL
Designer generates SQL that SQLite won't accept. For example --

sqlite> CREATE TABLE foo (
   ...> field1 INTEGER NOT NULL PRIMARY KEY ,
   ...> field2 INTEGER NOT NULL PRIMARY KEY 
   ...> );
SQL error: table "foo" has more than one primary key

The correct syntax is this --

CREATE TABLE foo (
field1 INTEGER NOT NULL,
field2 INTEGER NOT NULL,
PRIMARY KEY (field1, field2)  
);

AFAICT that's the only way to express a composite primary key. 

Here's where it gets tricky.

In issue 65 I pointed out how AUTOINCREMENT can only be used following a
PRIMARY KEY constraint. In other words, the only permissible syntax for
AUTOINCREMENT primary keys differs from the syntax for composite primary keys:

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

For single-column, non-AUTOINCREMENT keys, either syntax is acceptable:
CREATE TABLE foo (
id INTEGER NOT NULL PRIMARY KEY 
);

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

AUTOINCREMENT is not permitted on multi-column keys, so we can ignore that
case. (The SQLite docs say, "Multi-column primary keys do not become
aliases for the rowid. The AUTOINCREMENT keyword only works on a column
that is an alias for the rowid." http://www.sqlite.org/lang_createtable.html)

In short, it looks like one syntax is needed for AUTOINCREMENT primary keys
and another for composite primary keys. If someone attempts to create a
table that applies AUTOINCREMENT to one of the columns of a multi-column
primary key, it doesn't matter which syntax you use because SQLite won't
accept it.

Original issue reported on code.google.com by NikitaTh...@gmail.com on 24 Mar 2010 at 3:46

Attachments:

• invalid_example.xml

[GoogleCodeExporter]

Hi,

thanks for reporting this as well as providing a very detailed explanation. If 
your
observations are correct, I will have to apply some major refactoring to the 
relevant
XSL file :/

Original comment by ondrej.zara on 24 Mar 2010 at 10:39

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

[GoogleCodeExporter]

Sorry to hear it.

There is one possible workaround, but I think if I saw the behavior I'm about to
describe in WWW SQL Designer, I'd file a bug on that too. 

SQLite's FAQ says, "A column declared INTEGER PRIMARY KEY will autoincrement". 
http://www.sqlite.org/faq.html#q1

I didn't know this, so I learned something new (and useful!) today.

HOWEVER, the behavior a column declared like so --
   id INTEGER PRIMARY KEY 
differs subtly from one declared like so --
   id INTEGER PRIMARY KEY AUTOINCREMENT

The latter definition guarantees that keys will be unique over the life of the 
table
whereas the first definition can reuse keys (although it still auto-generates 
unique
keys which is all that most people really want when they create an AUTOINCREMENT
column). 

Here's an example showing re-use of keys.

sqlite> CREATE TABLE foo (id INTEGER NOT NULL PRIMARY KEY, junk TEXT);
sqlite> INSERT INTO foo (junk) VALUES ('x');
sqlite> INSERT INTO foo (junk) VALUES ('y');
sqlite> INSERT INTO foo (junk) VALUES ('z');
sqlite> SELECT * FROM foo;
id          junk      
----------  ----------
1           x         
2           y         
3           z         
sqlite> DELETE FROM foo WHERE id = 3;
sqlite> SELECT * FROM foo;
id          junk      
----------  ----------
1           x         
2           y         
sqlite> INSERT INTO foo (junk) VALUES ('abc');
sqlite> SELECT * FROM foo;
id          junk      
----------  ----------
1           x         
2           y         
3           abc       

Interestingly, Richard Hipp has pointed out that adding AUTOINCREMENT to a 
primary
key actually slows down SQLite a bit on inserts, because it has to check a 
table to
see what its next key will be.

So one could argue that AUTOINCREMENT is superfluous in SQLite and therefore 
WWW SQL
Designer doesn't need to include it in the SQL it generates for SQLite, but I 
don't
think that's a good workaround for this problem. IMO AUTOINCREMENT has three 
effects --
1) Slightly changes the behavior of the auto-generated keys.
2) Serves as documentation when looking at the CREATE TABLE statement.
3) Makes the generated SQL more portable to other databases.

In short, I think you're stuck with the ugly work of supporting the two 
different
syntaxes for PRIMARY KEY.

Original comment by NikitaTh...@gmail.com on 24 Mar 2010 at 2:14

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

[GoogleCodeExporter]

I fully agree. Omitting AUTOINCREMENT has very litle sense here; all your three
reasons are valid.

Original comment by ondrej.zara on 24 Mar 2010 at 2:38

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

[GoogleCodeExporter]

Fixed in r101, please re-test. I also added support for SQLite foreign keys.

Original comment by ondrej.zara on 9 Apr 2010 at 6:46

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

[GoogleCodeExporter]

Works! The SQL that's generated is formatted a little oddly with the comma on 
its own
line (as in the example below), but it is syntactically correct and SQLite 
accepts
it. Thanks!

CREATE TABLE experiment_metabolites (
experiment_id TEXT NOT NULL  REFERENCES experiments (id)
,
metabolite_id TEXT NOT NULL  REFERENCES metabolites (id)
,
PRIMARY KEY (experiment_id, metabolite_id)
);

Original comment by NikitaTh...@gmail.com on 12 Apr 2010 at 1:11

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

[GoogleCodeExporter]

I see - a fix will be commited soon.

Original comment by ondrej.zara on 12 Apr 2010 at 1:35

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