sql - 在数据库表中定义角色限制的更好方法

Question

角色表

RoleID Desc
1      primary
2      secondary
3      alternate

用户

UserID  Name
1       ann
2       saylor
3       jim
4       ken
5       kathy

路由表

RouteID   Name
1          x
2          y

RouteRoleUser 表

RouteID   RoleID      UserID
1         primary      ann
1         secondary    saylor
1         alternate    jim
1         alternate    ken
1         alternate    kathy

我有一个显示以下内容的网格:

Route | Primary Pumper | Secondary Pumper | Alternate Pumpers (comma separated)
x        ann                saylor           jim, ken, kathy

我的要求是:

• 任何路由只能有一个主要用户
• 任何路由都可以有0个或1个二级用户
• 任何路由都可以有 0 个或多个备用用户
• 一条路线的所有用户都是唯一的

如何从数据库设计的角度在 RouteRoleUser 表中进行需求限制？目前，如果我将 Route、Role 和 User 作为候选键，它不会阻止任何人为一条路线添加两个主要用户。

有没有更好的办法？

Answer 1

对于“任何路由都可以有 N 个 (类型) 用户”规则，您可以通过使用 INSTEAD OF INSERT 触发器并防止插入物。我个人在应用程序或存储过程级别处理此类逻辑。

对于“路由的所有用户都是唯一的”，您可以通过对 RouteID、UserID 的 UNIQUE 约束来强制执行此操作。