I have just begun experimenting with SQLite for an embedded project. I am
relatively new to this list, so apologies if this is old news. Is there an
archive of the emails with a good search facility so I can check for old
questions?
I was trying out some table creation and insert semantics in a simple test
app. I tried several variations on my create statement and the two PRIMARY
KEY syntax as listed at http://www.hwaci.com/sw/sqlite/lang.html. I have
noticed a number of important truths, a couple of which I question as bugs
(or user error ;).
1. In order to actually get autoincrement functionality, the table creation
has to specify the column explicitly as INTEGER without further
"decoration". I had tried specifying "UNSIGNED INTEGER (10)" and "INTEGER
(10)" as I would for mysql and I could not get autoinc to work. Is this
true? Is there a reason for this? It seems reasonable to specify the size
of an autoincrement integer as well as signedness. Or is it that all
autoincrement are 32-bit?
2. I tried specifying multiple columns as part of the primary key. If I
did it in the column definition I got back an error that there were
multiple columns in the primary key. If I did it as part of a general
constraint as "PRIMARY KEY (Col1, Col2, Col3)" the statement succeeded, but
the integer that was supposed to be autoincrement did not auto increment
even if specified correctly. If the only column in the list was the
integer column it worked fine and did the auto increment. So,
CREATE TABLE User ( Name VARCHAR (40),
UID INTEGER NOT NULL,
DeviceID VARCHAR (64) DEFAULT 'Unknown',
PRIMARY KEY (Name, UID, DeviceID) );
works, but
CREATE TABLE User ( Name VARCHAR (40) PRIKMARY KEY,
UID INTEGER PRIMARY KEY NOT NULL,
DeviceID VARCHAR (64) PRIMARY KEY DEFAULT
'Unknown' );
does not.
3. Related to #2 above, PRIMARY KEY in the list form does not seem to be
strict enough on unique when a null value is involved. If I have 3 columns
as part of the PRIMARY KEY list and do an insert that fills in 2 of the
fields I can repeat the insert and I get duplicate entries. E.g.:
CREATE TABLE User ( Name VARCHAR (40),
UID INTEGER,
DeviceID VARCHAR (64) DEFAULT 'Unknown',
PRIMARY KEY (Name, UID, DeviceID) );
INSERT INTO User (Name,DeviceID) VALUES ('Michael','Test');
INSERT INTO User (Name,DeviceID) VALUES ('Michael','Test');
Gets me two rows with ('Michael',NULLm'Test').
I think that 3 is a bug. I think 2 might hide a bug, unless I
misunderstand the syntax and intent (quite possible). 1 seems counter to
the goal of SQL syntax compatibility and should at least make it into the
FAQ that talks about AUTOINCREMENT.
If I'm missing something or wrong about any part, please let me know.
thanks. HTH.
Michael Hunley
Senior Engineer
PocketPurchase, Inc.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
