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

Question

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"

Answer 1

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 .

Answer 2

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.

Answer 3

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.