INTEGER is not the same as INTEGER(10) which is not the same as UNSIGNED INTEGER(10). Only the first form works for autoincrement.
I understand why the UNSIGNED INTEGER would be considered different, but I would argue that an autoincrement would actually be an unsigned integer, not signed. Not a big deal, just semantics since it works out the same. I do not understand why the specification of size would affect it. I can imagine how the code might work out that way, since the type is just a label in SQLite, but from a "writing generic code" standpoint it affects us users. If I had implemented some table creation support classes that wrap multiple SQL APIs (like MySQL and SQLite) I could not use the same type specifications since I need to specify size in MySQL, but doing so in SQLite actually breaks things. I can absolutely work around all of this, I'm just curious why the size part matters (no joke intended).
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.
Correct.
But why? They are functionally identical, as far as I can tell. Is there a difference, or is it just for simplicity of the SQLite code?
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 concur. The uniqueness constraint is not being enforced for multi-column primary keys. Why don't you write a ticket.
Will do (based on the follow up emails, actually).
thanks. michael
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
