Since many tables may refer to the incorrect id, updating the database can become a saga. So most of the time the id is never seen by a user. You can have keys that are unique to the table, or database wide unique keys. A key that's unique to the table is usually fine. To create an object, you'll need to get a key.
Some databases provide the ability to generate keys automatically.
With others you'll need to keep track of the next available key yourself. A common way to do this is to have a keys table with one row per table that keeps track of the next available key for that table. This table can be quite a bottleneck, so often it's best for a process to ask for many keys in one go. The conversion from IDs to references does not have to be done at load time. It is possible to store the id in the object's reference field and to perform the lookup as part of the accessor for that field.