[sqlite] autoincrement columns & primary key

Michael Hunley Mon, 08 Dec 2003 16:31:43 -0800

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]

[sqlite] autoincrement columns & primary key

Reply via email to