Your comment seems to conflate autogenerated IDs like MySQL AUTO_INCREMENT or PostgreSQL SERIAL with internal row IDs. Autogenerated keys are set on insert if you don't provide a value. After that they are stable. (And very popular, too.)
The case you describe sounds like using Oracle ROWID as a key, which I suppose people do as a hack to get around schema problems instead of fixing the schema. [1] That's an exceptionally bad idea.
Can you clarify how autogenerated keys are different from an autoincrement id column? In my experience, the autogenerated keys are built on top of auto-increment columns. Of course, if you use a stored procedure or something to generate a value, then you're just using a unique ID, same as anything else, but making your system dependent on the DB, which isn't awesome if you have a distributed system with replication and all that.
I might have missed something upthread but autogenerated keys to me just mean that the key is somehow generated automatically for you rather than using a natural key like social security number (SSAN) when inserting rows. Autoincrement IDs are one way of autogenerating a key that delegates generation to the DBMS server.
To expand on this, there are three common approaches to create automatic keys in SQL applications.
1.) Generate it in the application itself. You can make UUIDs yourself with a simple call in most languages. Ensuring uniqueness is your problem--to generate integers, for example, you'll need some sort of coordination if there's more than one application thread.
2.) Generate it from a SEQUENCE. Sequences are database-side key generators that hand back a unique sequence number from a block of available sequence numbers when you call for the next number. Uniqueness is pretty much guaranteed since numbers come from the shared database server. This approach is popular in PostgreSQL and Oracle.
3.) Generate it from an auto-increment column, such as MySQL AUTO_INCREMENT columns. Auto-increment columns generate the key server-side at insert time. Uniqueness is guaranteed, but you can't see the key until it has been inserted. If you need to know the key for follow-on INSERT or UPDATE commands you have to select it back using LAST_INSERT_ID(). [1] Auto-increment keys are popular in MySQL.
ORMs like Hibernate JPA tend to provide all of these as choices. I don't use ORMs much myself hence can't comment much on the details.
How is SEQUENCE different from AUTO_INCREMENT if you have only a single writeable db instance? If you dump & restore a table with SEQUENCE values as IDs, does the restored DB have the same IDs, or are they generated anew on restore?
Also, SSN is a terrible key [1] and getting uniqueness without central coordination can be done with UUIDv4 or ksuid [2], as long as you have a reasonably trustworthy source of randomness
With SEQUENCE it's up to you to select the ID and insert it as a column value in your code. With AUTO_INCREMENT the DBMS does it for you. They both depend on a central DBMS to generate values. In both cases you can restore data. The IDs are just normal column values after generation.
As far as SSAN I'm not asserting it's a good key, just a natural key, which is to say a key inherent in the record.
The case you describe sounds like using Oracle ROWID as a key, which I suppose people do as a hack to get around schema problems instead of fixing the schema. [1] That's an exceptionally bad idea.
[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseud...