postgresql - Do I need to declare a unique constraint for `bigint generated always as identity`?

Question

I'm creating a multi-tenant application and am prepending the tenant_id to all tables that my tenants will access. All of the tables will also have an incrementing surrogate key. Will I need to declare a unique constraint of the surrogate key or is that redundant?

CREATE TABLE tenant (
  primary key (tenant_id),
  tenant_id  bigint generated always as identity
);

CREATE TABLE person (
  primary key (tenant_id, person_id)
  person_id  bigint generated always as identity,
  tenant_id  bigint not null,

  unique (person_id),  -- Do I need this?

  foreign key (tenant_id) references tenant
);

question from:https://stackoverflow.com/questions/65947502/do-i-need-to-declare-a-unique-constraint-for-bigint-generated-always-as-identit

Answer 1

The primary key of a table should be a minimal set of columns that uniquely identify a table row. So that should be person_id, as it was specifically created for that purpose.

Add another (non-unique) index on tenant_id or (tenant_id, person_id) if you need to speed up searches based on tenant_id.