For example if I have an auto-numbered field, I add new records without specifying this field and let DB engine to pick it for me.
So, will it pick the number of the deleted record? If yes, when?
// SQL Server, MySQL. //
Follow-up question: What happens when DB engine runs out of numbers to use for primary keys?
NO. numerical primary keys will not reused, except you specify them manually(you should really avoid this!)
Depends on the auto-numbering system. If you're using a sequence of any kind, the numbers of deleted records will not get reused, as the sequence does not know about them.
Generally, no, the numbers are not reused.
However, you can -- in products like Oracle -- specify a sequence generator which cycles around and will reuse numbers.
Whether those are numbers of deleted records or not is your applications's problem.
Not specifically. If the key is being read from a sequence or autoincrementing identity column the sequence will just plug along and produce the next value. However, you can deactivate this (set identity_insert on on SQL Server) and put any number you want in the column as long as it doesn't violate the uniqueness constraint.
This question needs to be made more precise:
... "with Oracle Sequences"
... "with MySQL autonumber columns"
... etc...
As long as you create the table correctly you will not reuse numbers. However you can RESEED the identity column (IN MSSQL anyway) by using the following:
-- Enter the number of the last valid entry in the table not the next number to be used
DBCC CHECKIDENT ([TableName], RESEED, [NumberYouWantToStartAt])
This is of course insane... and should never be done :)
MySQL will not reuse IDs unless you truncate the table or delete from the table with no where clause (in which case MySQL, internally, simply does a truncate).
AFAIK, this could happen in MySQL:
How AUTO_INCREMENT Handling Works in InnoDB:
InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.
After a restart of server. Innodb reuse previously generated auto_increment values. :
Suggested fix: innodb table should not lose the track of next number for auto_increment column after restart.
Yeah, it really depends on the way you generate the id.
For example if you are using a GUID as the primary key, most implementations of getting a random new Guid are not likely to pick another guid again, but it will given enough time and if the Guid is not in the table the insert statement will go fine, but if there is already a guid there you will get a primary key constraint violation.
I consider the MySQL "feature" of reusing id's a bug.
Consider something like processing of file uploads. Using the database id as a filename is a good practice : simple, no risk of exploits with user-supplied filenames, etc.
You can't really make everything transactional when the filesystem is involved... you'll have to commit the database transaction then write the file, or write the file and commit the database transaction, but if one or both fail, or you have a crash, or your network filesystem has a fit, you might have a valid record in the database and no file, or a file without a database record, since the thing is not atomic.
If such a problem happens, and the first thing the server does when coming back is overwrite the ids, and thus the files, of rolled back transactions, it sucks. Those files could have been useful.