problem with auto increment primary key RRS feed

  • Question

  • hi all,

    i am having a problem with my auto-number key in a sql compact 3.5 sp2 database.  

    i have a table called "equipment"..the key is set to field "equipmentid", which is auto-number.  in my compact db, i can do one insert and save it.  if i try and insert another record, it complains saying i cant insert a duplicate into a unique index.  why isn't this auto-incrementing my key?  it looks broken to me.  my sql compact db has indentity seed of 0...the first insert sets the key as 0, even though the highest key is 7804, but it wont increment the next insert to 1.

    i read somewhere some were doing "ALTER TABLE MyTable ALTER COLUMN MyColumn INT IDENTITY (0, 1)"....why would i need to do this if it is already set like this?  is this a bug, or am i missing something?


    • Edited by Ge0rg3 Monday, November 12, 2012 10:37 PM
    Monday, November 12, 2012 10:24 PM

All replies

  • is the sql ce table created via provisioning?

    check Data Type Mapping and Considerations in the docs

    to quote:

    "Identity columns are copied from the server to the client database, but the
    identity seed and increment are always set to 0 and 1, respectively. This is
    regardless of how the properties were set in the server database. "

    and btw, you should avoid Identity columns when using synchronization...big potential for collisions.
    • Edited by JuneT Tuesday, November 13, 2012 12:52 AM
    Tuesday, November 13, 2012 12:37 AM
  • are you sure that sync is working at the first place ?

    the way you explain it means that it's not configured correctly at SQL Server

    Tuesday, November 13, 2012 11:18 AM
  • "are you sure that sync is working at the first place ?"  IT SEEMS as though it works...it syncs my new "0" record to the server...and the server takes that key.

    my point here is that it seems like sql compact autonumber doesn't  work as expected.  it should be auto-numbering just as access would or even sql server.  whatever the highest number is, add one to it.


    Tuesday, November 13, 2012 5:24 PM
  • avoiding identity columns is gonna be kinda difficult at this point.  the app is already in production, and i dont see a way to easily change out the id columns on all the existing tables.  i suppose sync would rather have a guid for a row key?  i guess so, according to http://msdn.microsoft.com/en-us/library/bb726011.aspx.  This would have been helpful to know when i wrote the first version of this app months ago.   i had no idea at the time i would be needing to write for occasionally-connected clients.

    hmmm......well i may have to be inventive for this thing.  once i set the id seed to 1, it started autonumbering from 1 (even though i have 7800+ records now).

    so here is my new solution:

    i will connect to my server with a download-only data-caching scenario.  when new records are added locally, they will be < 4000 (server equipment id numbering starts at 4500).  so i now know anything less than that is local to that client.  when connection is available, i will query my local table for anything less than 4000, and insert those records into the server's table (using server auto-numbering). ).  rinse and repeat until there is nothing under 4000.   then, after everything is happy, sync a fresh copy to the client.

    also by doing this, i no longer need to worry about conflcts, since each local db maintains it's own set of stuff <4000 until the next sync.

    seem reasonable?


    • Edited by Ge0rg3 Tuesday, November 13, 2012 10:06 PM
    Tuesday, November 13, 2012 7:40 PM
  • thanks for the articles.   compound key does look like the answer here!

    thanks again june!!!!


    Wednesday, November 14, 2012 4:14 PM