Auto Increment in primary Key evenThough there is Constrain voilation Exception Mysql(5.6.34)

Question

I have created a table User with following details

  {
       id , //autoincrment
       name
    }

and Table Account which has a User with foreign key relation

Account {
 id , //autoincrement
 userid //foreign key reference to user
 }

when i insert in to account with value userId which doesnot exist in user table i get Constraint voilation Exception

Cannot add or update a child row: a foreign key constraint fails (`schema`.`account`, CONSTRAINT `constraint_name` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`))

but my AUTO increment Count for Account got increased.

what i meant to say is before exception account table has max id of 100. when i insert new record it fails with above described exception.i corrected query and insert with correct userid then i see accountid value as 102.

is it a correct behaviour from mysql.should it increment autoincrement value even though query execution fails?

score 0 · Accepted Answer · answered Jun 08 '17 at 07:39

It's intended and correct, that auto_increment works outside the scope of transactions (like sequences in other database systems like oracle do as well). The reason is that two different transactions must not draw the same number, and a locking on such a neuralgic point as the key column of a table would have enormous performance impact in concurrent situations otherwise.

Cf. also this SO answer.

Auto Increment in primary Key evenThough there is Constrain voilation Exception Mysql(5.6.34)

1 Answers1