0

I have a table in which I took two field one is id that is primary key with Auto Increment attribute (tinyint) and another one is name (varchar(20)).

id tinyint(4) NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, PRIMARY KEY (id)

after that I have insert the values in table from 1 to 127 (max limit of tinyint). Now If I try to insert the record in table it gives error because I have reached at max limit of tinyint. I am fine with it. But If I delete all the records and table is empty then also I can't insert any record. I know I can use truncate here that will reset the primary key. But My question is here that why mysql doesn't insert the any available value(from 1 to 127) for primary key and If I manually insert the value for id from 1 to 127 it works.

insert into `new table` (id,name) values(1,'blahblahblah')  Working

insert into `new table` (name) values('blahblahblah') Not working

If I have a application with large database and I come this situation and any record insertion can fail in future then how can I know before occurred this. Is there any way by which I can insert the record(in empty table by delete all records) without truncating the table.

Sorry for my poor English.

Thanks

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
localhost
  • 483
  • 4
  • 10

1 Answers1

1

Mysql saves your AUTO_INCREMENT counter in its INFORMATION_SCHEMA table. I don't know which version you're using but you should read the docs about it.

As you can read here, you can set the number you want using

ALTER TABLE tablename AUTO_INCREMENT = 1;
William Monteiro
  • 46
  • 8