在SQL中通过数字限制多对多关系 [英] Limit many to many relationship by number in Sql

问题描述

三个表:用户，角色和数据透视表(很多)role_user.

Three tables: users, roles and a pivot table (many to many) role_user.

user:
- id
- name

role:
- id
- name

role_user
- id 
- user_id: foreign key link to user
- role_id: foreign key link to role

例如，如果我想将一个用户的最大角色数量限制为仅1个，则可以将role_id外部链接作为role_1字段放置在用户上，而不是使用多对多的数据透视表.

If I wanted to limit the amounts of maximum roles a user can have to only 1 for example, I could put the role_id foreign link on the user as a role_1 field instead of using a pivot table of many to many.

users:
- id
- name
- role_id_1

如果每个用户只希望有两个角色，情况也是如此.

The same goes if I wanted only two roles per user.

users:
- id
- name
- role_id_1
- role_id_2

如果我想使用数据透视表将数量限制为1、2或其他(不使用用户表上的外部角色链接)怎么办?在sql中有一个选项吗?

What if I wanted to limit the amount to 1, 2 or something else using a pivot table (Not using foreign role links on the user table) ? Is there an option for that in sql ?

类似于组合唯一索引选项，其中包括数据透视表中的role_id和user_id，但不是对唯一性的限制，而是对user_id出现次数限制的自定义约束.

Something like a composite unique index option including role_id and user_id in the pivot table, but instead of a constraint on the uniqueness, a custom constraint on the limit of the user_id number of appearances.

推荐答案

有一种无需触发器即可在SQL中实现的方法.这有点复杂，但是您可以做到.

There is a way you can implement this in SQL without triggers. It is a bit complicated, but you could do it.

首先添加另一个表.我称之为RoleNumbers.对于用户的每种可能角色，该表将由一行组成.因此，您可以为它设置1个，2个或任意多个角色.

It starts by adding another table. Let me call it RoleNumbers. This table would consist of one row for each possible role for a user. So, you set it up with 1, 2, or however many roles you want.

然后连接表:

create table UserRoles (
    UserRoleId int not null auto_increment primary key,
    UserId int not null references users(user_id),
    RoleId int not null references roles(role_id),
    RoleNumber int not null references RoleNumbers(Number),
    unique (UserId, RoleId),
    unique (UserId, RoleNumber)
);

这使用了我的命名约定.我在联结表上有一个合成键没有问题.

This uses my naming conventions. I have no problem with having a synthetic key on a junction table.

当插入新记录时，必须为尚未使用的RoleNumber分配一个值.因此，您获得了极限.执行此操作的最有效方法是通过触发器，但这并不是绝对必要的.您可以通过以下方式进行插入:

When you insert a new record, you would have to assign a value to RoleNumber that is not already being used. Hence, you get the limit. The most efficient way to do this is via triggers, but that is not strictly necessary. You could do an insert as:

insert into UserRoles(UserId, RoleId, RoleNumber)
    select $UserId, $RoleId, coalesce(max(RoleNumber), 0) + 1
    from UserRoles
    where UserId = $UserId;

delete将需要一个单独的查询来维护编号方案.

delete would require a separate query for maintaining the numbering scheme.

这篇关于在SQL中通过数字限制多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！