0

I am creating a new database for contacts and keeping the contact_id of the conatctscontract field as the primary key. My question is if anyone deletes a row in the contact then will the contact_id for that contact be reused by the android system. If so then how do I prevent that contact id from being used in my application or is there a better approach to this problem?

Developer Android
  • 577
  • 3
  • 5
  • 21

2 Answers2

1

I'm assuming your using MySQL. The database system auto_increments the contact_id primary key. When the row is deleted that id has already been "used" and is stored in the database as "used" so-to-speak, even though the row itself has been deleted. It will continue to stay that way as long as you don't reset the auto_increment starting point (which the syntax looks something like this)

ALTER TABLE contact AUTO_INCREMENT = 0;

The deleted contact_id's will not be reused if you use the auto_increment feature so long as you don't reset the auto_increment setting for that table (as far as my knowledge goes). It will only continue from the highest ID stored in the database.

Edit: If I'm not mistaken, the column using auto_increment is used in a special database statistics table (unavailable to you, but stored automatically from the database) which stores the latest "auto_incremented" number. Using the syntax above will reset the auto_increment back to 0 and it will resume from there, filling in the gaps that have been deleted.

kadeempardue
  • 89
  • 5
  • So if I have contact_id=20, and I delete it the contact_id 20 will never be used again right? (I am talking about the contactcontracts table in android) – Developer Android Dec 16 '12 at 04:57
  • SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes. – kadeempardue Dec 16 '12 at 06:02
  • Taken from the SQLite website. The answer is yes it cannot be used again unless you update it from the SQLLITE_SEQUENCE table. This is why they call it "auto" increment, meaning the database will automatically increment unless you change it somehow. If you wish to change the algorithm, take a look at this article for the syntax: http://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite – kadeempardue Dec 16 '12 at 06:03
0

If you are referring to the inbuilt contactsprovider database then both Contacts table and the Data table have id as AUTOINCREMENT.
So I dont think id will be reused.

You can check the codebase here

nandeesh
  • 24,740
  • 6
  • 69
  • 79