MySQL replace into behavior with unique constraint -

i have quick question mysql behavior.

imagine table 3(relevant) columns:

id (pk + ai),somedate,someuser,etc... 

i have put unique constraint on (date,user). when start clean test table , run following query twice:

replace `testtable` (somedate,someuser) values('2017-01-01','admin'); 

i expected row 'id' column on 1. instead everytime run query id goes because of auto increment , can't have happen (this corrupt data relations). why this? can make can keep original primary key when replace occurs?

not replace. that's insert preceded delete. behavior observe replace same behavior see if executed these 2 statements:

 delete `testtable` somedate = '2017-01-01' , someuser = 'admin';  insert `testtable` (somedate,someuser) values ('2017-01-01','admin'); 

and means auto_increment column on newly inserted row have new value.

---

perhaps consider using insert ... on duplicate key update.

reference: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

(note attempt insert row gets updated use auto_increment value.)