Are ids in mysql guaranteed not to be repeated, even if rows are deleted?

Of course if I put enough rows eventually there will be a repeat. But let's assume I choose a big enough id field.

I want to know if I can assume that the id uniquely identifies the row over time. And if the client sends me an id I want to be able to determine what row it refers or if the row was deleted (or if it is a fake id, but in that case I will not care telling, wrongly, that the row was deleted).

Please refer also to the following: if I restart the database, or backup and restore - will it be continue creating ids where it left - or may be it will decide to "fill in the holes"

From stackoverflow

• If you have a "int not null auto increment primary key" that you never reset, then yes IDs will not be reused.

  However, this raises an interesting question - what happens if you happen to reuse an old ID (even though it won't happen by default in MySQL, but the human factor always counts) ?

  If your database is properly normalized, cascaded and constrained your application should be able to handle the reuse of an ID.

  Edit (since you edited your post, I'll flesh out my answer), about this quote: "And if the client sends me an id I want to be able to determine what row it refers or if the row was deleted". It's always possible to determine what row an ID belongs to if it's not deleted (kind of vital to be able to extract information out of your database).

  However if the row the id refers to is deleted, then it's not possible to determine what row it belongs to... since it's not there. If you need this I would advice you to implement some type of auditing functionality, preferably by triggers .

  flybywire : and when do I reset it? Is it something I do with a command or can it happen as a side effect of something.

  thr : Well, it's not something that will likely happen - but it is possible through various sql commands in mysql.

  tehvan : If you're not certain about when to reset, then don't reset it. Automatically generated ids are not meant to be be meaningful nor reusable.

  thomasrutter : Usually an auto_incremement column will also be a primary key would it not. So in that case you wouldn't have to worry about manually re-using an ID, right? You'd get a clear duplicate value on primary key error.

  thr : thomsarutter: true

• The current autoincrement value is preserved across backup/restores via an extra attribute attached to the table. You can see it in a dump just after the ENGINE=:

  mysql> create table foo ( bar int(11) primary key auto_increment );
  Query OK, 0 rows affected (0.06 sec)
  
  mysql> insert into foo () values (), (), (), ();
  Query OK, 4 rows affected (0.00 sec)
  Records: 4  Duplicates: 0  Warnings: 0
  
  mysql> show create table foo \G
  *************************** 1. row ***************************
         Table: foo
  Create Table: CREATE TABLE `foo` (
    `bar` int(11) NOT NULL auto_increment,
    PRIMARY KEY  (`bar`)
  ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
  1 row in set (0.00 sec)
  

  You can reset it using "ALTER TABLE tablename AUTO_INCREMENT=", but it looks to me like the attribute value is ignored if it isn't more than the maximum existing id when you do an insert.

• also note that the described behaviour of not reusing a previously used ID is valid for the MyISAM engine, but actually is not valid for all available engines.