主页 / dba / 问题 / 316933
Accepted
JLCarveth
Asked: 2022-09-16 05:37:52 +0800 CST

检查是否存在外来关系

  • 772

我在 SQLite v3.37 中有几个表，用于跟踪Users、Roles和Permissions.

就这个问题而言，users只有一个id和一个email。rolesandpermissions也很简单，分别只有一个id和一个roleName和permName。

CREATE TABLE permissions (id integer primary key not null, permName text unique not null, dateCreated text default CURRENT_TIMESTAMP, dateUpdated text default CURRENT_TIMESTAMP);

我有两个辅助表，userRoles它们rolePerms分别跟踪为用户分配了哪些角色，以及分别为每个角色分配了哪些权限。

CREATE TABLE userRoles (userID integer not null, roleID integer not null, FOREIGN KEY (userID) REFERENCES users(rowid), FOREIGN KEY (roleID) REFERENCES roles(id));

这是架构的粗略图：

数据库架构图

我正在尝试从电子邮件地址和 a 创建一个 SQL 查询permName，确定该用户是否已被授予具有该权限的角色。

我试过写一个这样的查询：

SELECT users.rowid, users.email, roles.rowid, roles.roleName, permissions.rowid, permissions.permName, userRoles.*, rolePerms.* FROM users
    INNER JOIN userRoles on userRoles.userID=users.rowid AND userRoles.roleID=roles.roleID 
    INNER JOIN rolePerms on rolePerms.roleID=roles.rowid 
    WHERE users.email = '[email protected]' AND permName='testPermission';

但是这个查询显然没有完成工作。我见过这个关于加入 4 个表的问题，但是这些表都与一个表相关，而不是像我使用的模式那样的两个表。

编辑：我相信以下查询有效：

SELECT DISTINCT(permName) FROM users INNER JOIN userRoles ON userRoles.userID=users.rowID INNER JOIN roles ON userRoles.roleID=roles.id INNER JOIN rolePerms ON rolePerms.roleID=roles.id INNER JOIN permissions ON permissions.id=rolePerms.permID WHERE users.email='[email protected]';

最大的变化是roles通过 JOIN ON引用userRoles，否则 SQL 查询不知道表（？）

sqlite foreign-key

1 个回答

  1. Best Answer

    你的查询几乎是正确的，你只需要加入权限表，加上消除一些关于列名的混淆并删除一个额外的连接条件：

    SELECT users.rowid, users.email, roles.id, roles.roleName, permissions.id, permissions.permName, userRoles.*, rolePerms.* 
FROM users
INNER JOIN userRoles ON userRoles.userID=users.rowid
INNER JOIN roles ON roles.id=userRoles.roleId
INNER JOIN rolePerms ON rolePerms.roleID=roles.id
INNER JOIN permissions ON permissions.id=rolePerms.permID
WHERE users.email= '[email protected]' AND permissions.permName='testPermission';
    • 0

相关问题