一对一关系的外键 [英] Foreign Key to one-to-all relationship

问题描述

我想构建一个具有全局角色和自定义角色的多租户解决方案.
应用程序的授权将基于权限构建.
然后可能会定义一个结合了多个权限的角色.

I want to build a multi-tenant solution with global and custom roles.
The application's authorization will be built based on permissions.
Then there may be defined a role that combines multiple permissions.

我想提供几个可以供所有人使用的预定义全局角色.
另外，我想为租户提供定义自己的自定义角色的功能.

I want to provide several predefined global roles that can be used by everybody.
Additionally, I want to provide tenants with the ability to define their own custom roles.

此刻，我有以下方案设计草案(postgres):

At the moment, I have the following draft of the schema design (postgres):

create table tenants (
  id uuid primary key,
  ...
);

create table permissions (
  code character varying(30) primary key
);

create table roles (
  id  uuid primary key,
  tenant_id  uuid null references tenants,
  ...
);

create unique index on roles (id, tenant_id) where tenant_id is not null;

create table role_permissions (
  role_id uuid not null references roles,
  permission_id character varying(30) not null references permissions,
  unique (role_id, permission_id)
);

create table users (
  id uuid not null,
  tenant_id uuid not null references tenants,
  ...
  primary key (id, tenant_id)
);

create table user_roles (
  tenant_id uuid not null,
  user_id uuid not null,
  role_tenant_id uuid null,
  role_id uuid not null references roles,
  foreign key (user_id, tenant_id) references users (id, tenant_id),
  check (customer_id = role_tenant_id or role_tenant_id is null)
);

在这种模式下，我无法正确引用user_roles中的角色.

In this schema I'm not able to correctly reference roles from user_roles.

是否可以在postgres中实现这种约束?

Is it possible to implement such constraint in postgres?

推荐答案

我假设以users表的定义开头的对customer表和customer_id的引用确实意味着要引用tenant和tenant_id.

I assume that references to the customer table and customer_id starting with the definition of your users table really mean to refer to tenant and tenant_id.

在某些时候，您需要相信自己的代码是正确的.如果那对您来说还不够好，并且您必须有约束条件，那么这就是我会做的:

At some point you need to trust your code to be correct. If that is not good enough for you, and you must have constraints, then this is what I would do:

create or replace function user_role_check(_user_id uuid, _role_id uuid)
  returns boolean as $$
  select count(*) = 1 
    from roles r
         join users u
           on u.tenant_id = r.tenant_id
   where u.id = _user_id
     and r.id = _role_id;
$$ language sql;

create table user_roles (
  id uuid not null primary key,
  user_id uuid references users(id),
  role_id uuid references roles(id),
  check (user_role_check(user_id, role_id)),
  unique (user_id, role_id)
);

否则，您将无法将tenant_id复制到user_roles.

Otherwise you are stuck duplicating tenant_id into user_roles.

这篇关于一对一关系的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！