It does depend on the database as Joel said, and each database has its own special way of handing unique auto-generated keys. Its common though, to avoid using such a database-provided solution since it is not often portable (nor is it scalable), and to focus on manual-application controlled solutions. This also helps for distributed environment where keys may need to be unique accross multiple databases.
In general for small applications, use the database solution, but if its a large scale application, write your own unique key generator. If you do though, performance is something
you should be aware of since you wouldn't want the system querying the database for every single generated key (more likely you'd want a cached set of data and update the database at set intervals).