It's definitely not a surrogate key; It's a natural. A surrogate key is something you add to the data. If it's provided by the client and specific to their data, it's a natural key for your purposes.
It's like a UPC or the like. If none of that makes sense, for your purposes it needn't be there. Architecturally, you'll likely never use it and you'll never need it. You also aren't free to change it. That's all indicators that it's natural. However, I wouldn't call it "TENANT_CUSTOM_ID". I'd go for something more regular, like "SERIAL_NO." That's normally how you link out to a tenant/vendor/supplier database.
Update, if you're generating it.
If you're generating it, it's a surrogate key. A second surrogate key. But what's the point? You should never generate two surrogate keys. If you just need to hide your own primary key, consider
- generating a single UUID instead, UUIDs are slightly bigger but it will save you the trigger.
If you're not deleting the rows, considering using a window-function with
row_number() OVER (PARTITION BY tenant ORDER BY id).
If you absolutely must have the tenant-specific id, I would further suggest dropping your regular
id and going with a composite key
tenant, tenant_custom_id. You may as well make it crucial to the system.
I also wouldn't call it
_custom_id if it's not custom and you're generating it.
Lastly, I wouldn't bug about using the same pkey on multi-tenant. It's quite normal.
From Wikipedia, (take with a grain of salt)
A surrogate key (or synthetic key, entity identifier, system-generated key, database sequence number, factless key, technical key, or arbitrary unique identifier) in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key which is derived from application data.
A natural key (also known as business key) is a type of unique key, found in relational model database design, that is formed of attributes that already exist in the real world. It is used in business-related columns. In other words, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called domain key.