SQL table row ID

incarnate

For my tables I use the auto_increment for the ID, say a few rows get deleted, is there any way to make newly inserted rows use the deleted rows' IDs instead of truncating the whole table?

Thanks.

rachel2004

Assume you've done the following:

DELETE
FROM users
WHERE userid = 20
LIMIT 1;

So now the userid "20" is free and if you did a "SELECT userID FROM users", you would get something like:

17
18
19
21
22
etc.

To use userid 20 again, you simply need to identify the column explicitly in your INSERT statement:

INSERT INTO users AS u
(userid, firstname, lastname)
VALUES
('20', 'bubba', 'gump');

incarnate

Is that the only way to do it? My tables have over 10,000 rows and a lot get deleted everyday. Then every time a row gets deleted I'll have to save the ID?

incarnate

bump

dnast

Either that or retrieve all IDs and figure out which ones are missing. It's probably not worth it, whichever way you choose.

rachel2004

To expand on what dnast has said (because I agree with dnast), when you built you column for the "id", you had the option of specifying it NOT NULL. Since the "id" column can never be null, you'd have to loop through the table and output a list of record IDs that are not being used (as opposed to another way which I won't go into). But why? Granted, to us as human beings, things seem much nicer when we have an ordinal list with no gaps. But what does the database care? It will work the same with an "id" of 10 as it would with an "id" of 10000.

incarnate

Yeah I knew those ways, was just wondering if there was some SQL command/thing or something.

rachel2004

You can resequence the column. Not advised, but if you wish to do it...

ALTER TABLE table_name
DROP id_column,
ADD id_column INT NOT NULL AUTO_INCREMENT FIRST,
AUTO_INCREMENT = 1;

This will start the sequencing at integer 1. See MySQL manual for full syntax on ALTER TABLE.