[SOLVED] How to Prevent Reuse of Deleted Primary Key Values in Table?

Issue

I have a table with the primary key set to auto increment. Sometimes, records get deleted. When this happens, I DO NOT want to reuse the deleted ‘recID’ values … but that’s what happens!

For example, in the table definition exported by phpMyAdmin below, the table should start numbering at 19 but it shows 13.

How can I prevent the re-use of deleted primary key values?

CREATE TABLE scans (
  recID` int(11) NOT NULL,
  mac varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE scans
  ADD PRIMARY KEY (recID);

ALTER TABLE scans
  MODIFY recID int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

Solution

The only foolproof solution is to not delete the rows. Or at least don’t delete the row with the highest auto-increment value.

Answered By – Bill Karwin

Answer Checked By – Jay B. (BugsFixing Admin)