How to Prevent Reuse of Deleted Primary Key Values in Table?-CodePudding

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 exported table definition 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;

CodePudding user response：

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