in

System.Data.SQLite

An open source ADO.NET provider for the SQLite database engine

Overwrite autoincrement field

Last post 09-11-2010 7:21 AM by Robert Simpson. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next
  • 09-09-2010 4:19 AM

    Overwrite autoincrement field

    In MS SQL I would use SET IDENTITY INSERT ON How do I do something similar in SQLite. I am trying to upgrade a database and want to maintain the IDs from the original Thanks
  • 09-10-2010 11:55 AM In reply to

    • arit
    • Not Ranked
    • Joined on 06-28-2010
    • Posts 2

    Re: Overwrite autoincrement field

    First, I think you're referring to SET IDENTITY_INSERT ON (underscore), which --as far as I remember allows you to provide your own unique ID values (MSSQL Server).

    Second, provided I understand your question correctly, you shouldn't have to worry about that in SQLite3, since what you're trying to do is its 'default' behaviour.

     

    Example (in SQLite):

    Let's create a new table. The table consists of an ID attribute and some Name attribute. The ID field is defined as an integer PRIMARY KEY.

    Now, let's add some values to this new table. At first we'll let SQLite take care of the ID value, then we'll manually provide a (unique) ID value, and lastly we'll have SQLite generate the ID value once again automatically...

     BEGIN TRANSACTION;

    CREATE TABLE IF NOT EXISTS MyColorsTable(ColorID integer PRIMARY KEY, ColorName text);

    --create a record and let SQLite create the ID value
    INSERT INTO MyColorsTable(ColorName) VALUES('Red');
    INSERT INTO MyColorsTable(ColorName) VALUES('Green');

    --manually provide an ID value now
    INSERT INTO MyColorsTable(ColorID,ColorName) VALUES(456,'Blue');
    INSERT INTO MyColorsTable(ColorID,ColorName) VALUES(789,'Purple');

    --and lastly let SQLite do the job of providing an ID...
    INSERT INTO MyColorsTable(ColorName) VALUES('Brown');
    INSERT INTO MyColorsTable(ColorName) VALUES('Black');

    COMMIT;


    The resulting table values are shown below and I believe is something what you're trying to accomplish...

    SELECT * FROM MyColorsTable

    ColorID     ColorName
    1               Red
    2               Green
    456            Blue
    789            Purple
    790            Brown
    791            Black

     Again, not sure if that's what you were asking, but that's the closest I could think of. 

  • 09-11-2010 4:55 AM In reply to

    Re: Overwrite autoincrement field

    Thanks thats what I've worked out too, but E.F doesnt appear to be doing that, any ideas how I can force it to?!
  • 09-11-2010 7:21 AM In reply to

    Re: Overwrite autoincrement field

    I wouldn't use the E.F. to migrate a database -- it's too much overhead, and very simple things like inserting into an identity column become a nightmare because the E.F. won't let you do it.

    You're much better off going straight to the metal.

Page 1 of 1 (4 items)
Powered by Community Server (Commercial Edition), by Telligent Systems