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.
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.
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.
