主页 / user-260704

JLCarveth's questions

Martin Hope
JLCarveth
Asked: 2022-09-28 05:18:09 +0800 CST
  • 1

我有一个包含以下表格的数据库：items、categories和category_items. （简化，省略字段）模式如下：

CREATE TABLE IF NOT EXISTS items (
    id SERIAL primary key not null,
    partNo text unique not null
);

CREATE TABLE IF NOT EXISTS categories (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS category_items (
    category_id SERIAL PRIMARY KEY,
    partno TEXT NOT NULL,
    FOREIGN KEY (partno) REFERENCES items(partno),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

在我想在表中插入一条记录之前，这个模式似乎工作正常category_items。在第一次插入时category_items，它可以工作，但是如果我尝试插入同一类别的另一个项目，则会出现重复键错误：duplicate key value violates unique constraint "category_items_pkey" Detail: Key (category_id)=(2) already exists.

问题是，category_id从来没有指定必须是唯一的，那么为什么这会导致约束错误呢？

这些是我试图插入的记录：

INSERT INTO category_items (category_id, partno, size) VALUES
    ((SELECT id FROM categories WHERE name = 'category1'), 'part1', '1"');
    
INSERT INTO category_items (category_id, partno, size) VALUES
    ((SELECT id FROM categories WHERE name = 'category1'), 'part2', '4"');

使用 PostgreSQL 14。另外，如果这很重要，我正在 sqlfiddle.com 上运行它

postgresql
Martin Hope
JLCarveth
Asked: 2022-09-16 05:37:52 +0800 CST
  • 0

我在 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