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

角色表

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个或一个辅助用户

任何路由都可以有0个或多个备用用户

路线的所有用户都是唯一的

从数据库设计的角度来看，如何在RouteRoleUser表中设置需求限制？目前，如果我将“路线”，“角色”和“用户”设为候选键，则不会
阻止任何人为路由添加两个主要用户。

有没有更好的办法？

最佳答案

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

对于“路由的所有用户都是唯一的”，您可以在RouteID，UserID上使用UNIQUE约束来强制实施。