Serial sequence per tenant
I am building a system where I have a multi tenant setup, each tenant can create a tasks. For each task a tenant creates I want the task to have an incremental ID (TASK-1, TASK-2, TASK-3).
So the database schema would be something like this
id: serial
task: text
tenantId: uuid
And then I want to have a composite primary key made up of ID and TenantID. But that the serial for each tenant is incrementing independently from each other. Is this possible?
The database is postgres
1 Reply
More of a postgres question. But I think you'll need a trigger or per-tenant sequence. In your example either make it part of the application logic (max()+1) or use a trigger that does the same thing, just not in the app logic but instead in the database.
Also, not what you asked, but I personally would not make this the primary key, but just an indexed column instead.