常规的constraint不能跨多个表，但是您可以使用constraint trigger来强制执行完全任意的约束逻辑。

create table groups(   
    id serial primary key );
create table firms(    
    id serial primary key,
    group_id int references groups(id) );
create table companies(
    id serial primary key,
    firm_id int references firms(id),
    name text );

x

create function f_unique_company_name_per_group()returns trigger as $f$
declare current_company_group int:=(select group_id 
                                    from firms 
                                    where id=new.firm_id);
begin 
    if exists(select from companies inner join firms
             on  companies.firm_id=firms.id
             and companies.name=new.name
             and companies.id is distinct from new.id
             and firms.group_id=current_company_group) then
        raise exception using 
            detail=format('Company name %L already exists in group %L',
                               new.name,
                               current_company_group),
            errcode = 23505, --'unique_violation',
            column='name',
            table=TG_TABLE_NAME,
            schema=TG_TABLE_SCHEMA,
            datatype='text',
            constraint='t_unique_company_name_per_group';
    end if;
    return new;
end $f$ language plpgsql;

create constraint trigger t_unique_company_name_per_group 
after insert or update on companies
for each row execute function f_unique_company_name_per_group();

insert into groups values (1),(2);
insert into firms values (1,1),(2,1),(3,1);
insert into companies values (1,1,'company1'),(2,2,'company2');
--different company, different firm, but the same name within group:
insert into companies values (3,3,'company1');

ERROR:  23505
DETAIL:  Company name 'company1' already exists in group '1'
CONTEXT:  PL/pgSQL function f_unique_company_name_per_group() line 9 at RAISE

的数据