How can I make SQL Auto Increment not skip removed ids?

 In SQL, when you use an AUTO_INCREMENT column, it generates a new unique value for each inserted row, and these values are incremented regardless of whether a row is deleted. If you delete a row, the next inserted row will still get the next number in the sequence, even if that number was previously used.

By design, this is how most database systems behave. However, if you want to ensure that the sequence of AUTO_INCREMENT values doesn't skip over deleted IDs, there are a few strategies you could consider, although they often come with trade-offs.

1. Reset the AUTO_INCREMENT Sequence Manually

If you want to reassign IDs or prevent gaps after deletions, you can reset the AUTO_INCREMENT sequence manually. This can be done by altering the sequence to match the current highest ID or any specific value you prefer.

For MySQL:

ALTER TABLE your_table AUTO_INCREMENT = 1;

You can set the AUTO_INCREMENT value to the next number you want. If you want to avoid large gaps, you can set it to the highest current ID value + 1.

For PostgreSQL: In PostgreSQL, sequences are separate from tables, so you'll need to update the sequence associated with the SERIAL or BIGSERIAL column:

SELECT setval(pg_get_serial_sequence('your_table', 'your_column'), (SELECT MAX(your_column) FROM your_table));

This command updates the sequence so that the next inserted row gets the next available number based on the highest value already in the table.

Note: While this method works, it doesn't "fill in" the gaps, it just adjusts the sequence.

2. Reorganize the Table to Reuse Deleted IDs

You can reuse IDs from deleted rows by performing a cleanup operation that fills the gaps. This approach is more complex and involves:

1. Identifying Deleted IDs: Query the database to find the deleted IDs.
2. Insert IDs Manually: Insert new rows with the IDs of the deleted rows.
3. Manually Set AUTO_INCREMENT Sequence: After reinserting, adjust the AUTO_INCREMENT value to ensure new rows continue from the correct value.

This process can become tricky and may not always be efficient depending on the number of deletions and how frequently the table grows.

3. Custom ID Management

If you really need to fill gaps in your AUTO_INCREMENT values (i.e., to make sure that no IDs are skipped at all), you could abandon AUTO_INCREMENT and implement a custom mechanism for generating IDs. This could involve:

• Storing a list of available IDs and assigning them manually.
• Using a more complex algorithm to generate IDs that fills in gaps or reuses deleted IDs.

Example: You could implement a custom ID generator with a stored procedure or a trigger that reuses deleted IDs, though this requires careful handling of concurrency and performance concerns.

4. Use a Separate Sequence for IDs

Another solution is to create your own sequence in the database, separate from the AUTO_INCREMENT mechanism. This gives you full control over how IDs are generated, allowing you to implement logic to reuse deleted IDs if needed.

For PostgreSQL: You can create your own sequence:

CREATE SEQUENCE custom_id_seq START 1;

And use NEXTVAL to fetch the next available ID:

SELECT NEXTVAL('custom_id_seq');

You can then implement your own logic for handling deleted IDs (e.g., by inserting previously deleted IDs into the sequence when rows are deleted).

5. Using a "Soft Delete" Approach

If you absolutely want to avoid skipping IDs, you might consider using a "soft delete" approach. Instead of deleting rows, you could add a deleted_at timestamp (or similar) to the table and set this field to the current timestamp when a row is deleted. This way, the row still exists, and the AUTO_INCREMENT values are not skipped. When you query the table, you simply filter out rows where the deleted_at field is not NULL.

Example:

ALTER TABLE your_table ADD COLUMN deleted_at TIMESTAMP;

Instead of deleting a row:

UPDATE your_table SET deleted_at = NOW() WHERE id = X;

This way, the AUTO_INCREMENT values are not affected, and no IDs are skipped.

Conclusion

The default behavior of AUTO_INCREMENT is to increment regardless of deletions, which results in gaps. If you want to prevent gaps, you can either manually reset the sequence, implement a custom ID management system, or use a "soft delete" approach to avoid deleting rows entirely. Each method has its own trade-offs in terms of complexity and performance, so choose the one that best fits your requirements.