Toad World® Forums

Autoincrement Format


#1

Hello,

I have a clarification on autoincrement function in a PK column. I created a data model/table with autoincrement enabled to a PK column and the initial autoincrement is starting from 1.

To test the table in MySQL database, I inserted 10 rows and by default PK column assigned with the sequence number 1 to 10. In turn I tried inserting another row to see if the autoincrement works by selecting the next sequence number 11, instead it picked some other random number. I think I am missing something here while creating data model.

  1. In Table options I could see a list of Row Format, from the list I picked "Dynamic". Please confirm if this it correct or which option I should pick from the drop down for autoincrement. Screenshot attached.

Or

  1. Please let me know what needs to be enabled in data model to pick correct sequence number in the autoincrement PK column.

Thanks !!
Rajesh


#2

Hi Rajesh,

This is weird. I test following commands and no problem:

create table tabauto (attrauto int AUTO_INCREMENT PRIMARY KEY, attr2 int)
  AUTO_INCREMENT = 1;

insert into tabauto (attr2) values (1);
insert into tabauto (attr2) values (2);
insert into tabauto (attr2) values (3);
insert into tabauto (attr2) values (4);
insert into tabauto (attr2) values (5);
insert into tabauto (attr2) values (6);
insert into tabauto (attr2) values (7);
insert into tabauto (attr2) values (8);
insert into tabauto (attr2) values (9);
insert into tabauto (attr2) values (10);

select * from tabauto;

-- all records are OK

insert into tabauto (attr2) values (11);

select * from tabauto;

-- last inserted record is OK (autoincrement value is 11)

ROW_FORMAT option in MySQL define the physical format in which the rows are stored. It does not affect autoincrement behavior.

Regards,

Mario