AUTO_INCREMENT question for mysql

If I understand this correct, AUTO_INCREMENT= is only helpful for tables which are self growing, like a user log table, user details table, etc need to insert/delete records dynamically. But for lookup tables like cities, languages, etc which I manually need to inset/delete data there is no benefit correct, esp since I have to create the FK for these in other tables so if i manually create the PK ID then i can match it properly as FK in other tables vs alowing the system to crate auto create PK and th开发者_StackOverflow中文版en i have to find it and use those in other tables as FK?

I don't believe that it's mandatory to have an auto_increment column. Primary key must be unique, and you should have some column (or set of columns) with a primary key constraint in every table. But using an automatic pseudokey is not required.

The advantage of auto_increment is that it guarantees no two inserts will generate the same number, even if they're running in concurrent transactions. You can't do this manually without locking, which effectively queues up your clients and hurts throughput.

I wrote a chapter about this issue in my book SQL Antipatterns.

Every table should have a primary key. So setting the primary key with AUTO_INCREMENT is always useful.

Using an auto_increment as the PK in a cities table ensures that subsequent inserts and deletes do not cause foreign references to suddenly reference the wrong thing. (I.e. prevents inadvertent reuse of a key value).