I am evaluating SQLite for a project I am working on. I have a question about the behavior of the INTEGER PRIMARY KEY auto increment feature.
My platform uses 32 bit integers, so the valid values for an unsigned integer are 0 - 4294967296 and the valid values for a signed integer are -2147483648 - 2147483647. Since the INTEGER PRIMARY KEY data type is a signed integer, the maximum positive value is 2147483648. If my table already has a row with the maximum positive value in the primary key field, and I insert a row using NULL as the value of the primary key field, the row is inserted and the primary key is assigned the value of -2147483648. That makes sense to me and I have no problem with that. The problem is that the next row I insert generates the error "SQL error: PRIMARY KEY must be unique". I suspect that this is because SQLite tries to use the next largest positive value when it increments the primary key field. Is there an easy way to cause the INTEGER PRIMARY KEY column to use an unsigned integer instead, or to roll over to 0 instead of the most negative value for the data type? I suspect that in practice I will not run into this issue. However, I would feel better knowing that there is no chance that I will encounter this problem. Thanks, Shawn
