I’m working on a project in which I’m importing data from a MS Access database. In the case of one table, the data source has an integer primary key which, for a number of reasons, it’s important to retain in my database. These ids are kind of spread out all over a range from 1 to 10,000 or so with significant gaps between, etc.
Once I’ve imported the data, I would like to be able to use an auto incrementing primary key. Is there any way to create a SQLite table with an integer primary key to which the existing IDs will be imported, and then make it auto-incrementing? (I’m guessing that it’s not possible, but thought I’d ask here before trying to figure out a more complex solution.
I may stand corrected here but I believe you can import an id into an an auto increment integer primary key field provided that id does not already exist in the table you are importing into. Therefore you should be able to import into your new table without changing the ID field from int pri key to AI pri key.
I have just tested that on MySQL and SQLite without an issue.
Wow; Thanks to both of you! I didn’t expect it to be easy.
Just to be clear, Mike, you’re saying that you defined the field with AUTOINCREMENT from the outset, and that did nonetheless allow you to import explicit values? And Simon, your saying that any integer primary key will auto-increment, even if it’s not specified to?
An autoincrement key will increment ONLY if it is not specified in the insert statement. If you specify a value, that’s the value that gets inserted (as long as there is no collision, then nothing gets inserted).
Do you mean that if a collision does occur that a record does get inserted, but we end up with a NULL value in the primary key, or that no record gets inserted? I would have guessed that the primary key carries an implicit NOT NULL constraint, and that this would raise an error with no record being inserted.
I’m sorry I can’t mark multiple responses as having answered my question, but that’s how it is. Nonetheless, thank you to all three of you as each of you did answer my question, and each of you added an additional bit of information.
I just tested this (on cubeSQL server as I kinda lied by ommission in my original post), and found that you are all correct. Answering my own last post, it turns out that the primary key IS treated with as though a UNIQUE constraint had been specified, and an error is raised.
Thanks so much to all of you, Simon, Mike, and Tim!
[quote=46212:@Peter Truskier]Thanks, Tim. When you say:
Do you mean that if a collision does occur that a record does get inserted, but we end up with a NULL value in the primary key, or that no record gets inserted? I would have guessed that the primary key carries an implicit NOT NULL constraint, and that this would raise an error with no record being inserted.[/quote]
Just to clarify, it does imply a NOT NULL constraint, but it also implies a DEFAULT constraint, which is the autoincrement value. You can leave the column out of the insert statement (implying a null) or you can insert an explicit NULL into the column to cause it to autoincrement. The only error will come from the implied UNIQUE constraint.
Sorry, I’m a little late to this thread but I just want to clarify.
If you do an insert and SPECIFY a value for the auto increment primary key, it will INSERT that value as long as there are no collisions (as in duplicates).
If there is a duplicate, does the record get inserted with a unique value or does it get omitted?
If you do an insert and DON’T SPECIFY a value for the auto increment primary key, it will assign one automatically.