restarting auto incrementing

I have a table with an id field, set to auto increment.

However, say I insert 10 records, and then empty the table, the first new record will be id number 11.

Why is this, and is there a way to prevent it from happen开发者_如何学编程ing?

The auto increment isn't reset automatically following a delete

TRUNCATE TABLE tbl_name

will delete all records and reset the Auto Increment field or you can use

ALTER TABLE tbl_name AUTO_INCREMENT=1

Source

They behave that way to maintain referential integrity. You can't just decide that you're going to re-number your ID columns because that could, for example, violate primary and/or foreign key constraints. Check out the article, maybe it will make more sense. It's kind of an in-depth topic for a beginner in the relational database world.

If you are just beginning out, I would suggest doing some more research into the constraints and relational database modeling in general.