What you’re trying to do sounds dangerous, as that’s not the intended use of AUTO_INCREMENT. If you really want to find the lowest unused key value, don’t use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice. Take a step back and ask “why you need to recycle key … Read more
If you really need this you can achieve your goal with help of separate table for sequencing (if you don’t mind) and a trigger. Tables CREATE TABLE table1_seq ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE table1 ( id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT ‘0’, name VARCHAR(30) ); Now the trigger … Read more
Postgres 10 or later (serial columns remain unchanged, see below.) Consider a standard-SQL IDENTITY column. Can be GENERATED BY DEFAULT or (stricter) GENERATED ALWAYS. Basics in the manual for CREATE TABLE. Details in this blog entry by its principal author Peter Eisentraut. Create table with IDENTITY column CREATE TABLE staff ( staff_id int GENERATED ALWAYS … Read more
You can reset the counter with: ALTER TABLE tablename AUTO_INCREMENT = 1 For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel): Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if … Read more
There is no such thing as “auto_increment” or “identity” columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger: Table definition: CREATE TABLE departments ( ID NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL); ALTER TABLE departments ADD ( CONSTRAINT dept_pk PRIMARY KEY (ID)); CREATE SEQUENCE dept_seq … Read more