如何使用相同的约束条件映射不同的多对多关系

Question

我有一个数据模型，我对它有些担忧。以下是该模型：

我的担忧是，可能会将一个应用程序分配给一个成员，然后将来自不同应用程序的角色分配给该成员。

现在，我知道我可以对此设置约束以确保不会发生这种情况，但那似乎只是一种权宜之计。我更愿意设计模型，以便不需要约束。

有人能建议如何修改模型以确保成员只能被分配来自其所分配的应用程序的角色吗？

- Erik Funkenbusch

---

您是在说一个成员只能分配给一个应用程序吗？还是说每个应用程序需要有一个单独的角色？ - BRPocock

在你的情况下，我没有看到任何约束条件有问题。我能想到的任何修复方法都需要进行一些丑陋的去规范化处理... - Michael Fredrickson

在我看来，模型没问题。你想要实现的更像是业务逻辑/应用级别的验证。你的应用程序应该通过遍历关系来强制执行完整性。如果你想防止应用程序从数据库端进行操作，你可能需要考虑将其包装在一个过程中。 - William Stearns

@Mystere Man，根据您的业务规则，一个成员可以被分配到一个应用程序，但不一定被分配到该应用程序分配的角色吗？ - Tim Lehner

@TimLehner - 不可以，一个用户必须被分配到至少一个应用程序，并且至少从该应用程序中分配一个角色。他们可以被分配到许多应用程序，并分配来自这些应用程序的角色。他们不能被分配到未分配给他们的应用程序的角色。 - Erik Funkenbusch

Answer 1

通常，当您拆分一个键时，会遇到这种问题。修复该拆分键，然后使用重叠的外键约束通常是您要寻找的解决方案。

create table cmember (
  cmemberid integer primary key,
  username varchar(15) not null,
  emailaddress varchar(64) not null
);

create table application (
  applicationid integer primary key,
  description varchar(50) not null
);

create table member_application (
  cmemberid integer not null references cmember (cmemberid),
  applicationid integer not null references application (applicationid),
  primary key (cmemberid, applicationid)
);

create table role (
  roleid integer primary key,
  rolename varchar(25) not null
);

create table crole (
  croleid integer not null references role (roleid),
  -- Include the application id in this table . . .
  applicationid integer not null references application (applicationid),
  -- and make it part of the primary key.
  primary key (croleid, applicationid)
);

create table member_role (
  cmemberid integer not null references cmember (cmemberid),
  croleid integer not null,
  applicationid integer not null,
  primary key (cmemberid, croleid, applicationid),
  -- Note the overlapping foreign key constraints.
  foreign key (croleid, applicationid) references crole (croleid, applicationid),
  foreign key (cmemberid, applicationid) references member_application (cmemberid, applicationid)
);

insert into cmember values (1, 'A', '[e-mail]');
insert into cmember values (2, 'B', '[e-mail]');

insert into application values (1, 'App 1');
insert into application values (2, 'App 2');

insert into member_application values (1, 1);
insert into member_application values (2, 2);

insert into role values (1, 'Admin');

insert into crole values (1, 1);
insert into crole values (1, 2);

insert into member_role values (1, 1, 1);
insert into member_role values (2, 1, 2);

成员1只被分配给应用程序1。因此，尝试插入引用应用程序2的行应该会失败。

insert into member_role values (1,1,2);
ERROR:  insert or update on table "member_role" violates foreign key constraint "member_role_cmemberid_fkey1"
DETAIL:  Key (cmemberid, applicationid)=(1, 2) is not present in table "member_application".

- Mike Sherrill 'Cat Recall'

Answer 2

是的，

方法是从Member_Role中删除对CMemberID的外键，并在该表（Member_Role）中创建一个指向Member_Application的外键。新的外键必须包含两个字段：ApplicationID + CRoleID。

现在：

Member_Role ( CMemberID , CROleID )
PK = CMemberID + CROleID
FK1 (to CMember) =  CMemberID
FK2 (to CRole) = CRoleId

解决方案：

CRole: Create unique constraint on CRoleID + ApplicationID
Member_Application: create unique constraint on CMemberID + ApplicationID
Member_Role ( CMemberID , ApplicationID, CRoleID )
PK = CMemberID + ApplicationID + CRoleID
FK1 (to Member_Application) = CMemberID + ApplicationID
FK2 (to CRole) = ApplicationID + CRoleID

- dani herrera

---

似乎这仍然允许将任何角色连接到该成员应用程序。 - Tim Lehner

要将一个角色连接到成员应用程序，CRoleID和ApplicationID必须匹配。只有在最后的限制条件下，应用程序成员才能获取应用程序角色。 - dani herrera