Look what I tried but gives error:

Error Code: 3818. Check constraint 'teste_chk_1' cannot refer to an auto-increment column.

create table teste(
id int auto_increment check(id >= 0 and id<3),
nome varchar(10) not null,
primary key(id)

As the error message states, you have an issue with the check constraint having an upper limit check, which will cause problems with an AUTO_INCREMENT option enabled. If this table must have an id value of 0, 1, or 2, and you do not want to manually specify it at the time of record insertion, then you will need to use a BEFORE INSERT trigger to determine which id value is available and assign it.


Not the answer you're looking for? Browse other questions tagged or ask your own question.