SQL auto_increment behaviour

My first question is:

• Can I manually input ID into an auto_increment column of a table? For example: I have 10 records with ID from 1 to 10. Can I manually input the 11 record with the ID of 3 (yes, it’s a duplication!)

My second question is:

• I assume that it’s possible to manually input ID number, what if I have 9 record with ID from 1 – 9, and my 10th record have the ID of 11. Is the “Auto_increment” gonna skip it to go to 12, or it will add another 11?

I know I didn’t express it so well, but I’m confused and hope someone can help!!

Answer

1. No you can’t. An auto_increment column can’t contain duplicates and is often used as a primary key. It’s possible to have a non-PK auto_increment column as long as you have index on it. There can be only one auto_increment column per table.
2. Yes, it’s possible to insert a value manually into auto_increment field. It will start at MAX value + 1. Therefore you’ll have a gap in sequence.

Here is SQLFiddle demo

Further reading Using AUTO_INCREMENT