无中间表时，如何确保Permission的resource_id仅存在于Resource1或Resource2

针对需求——保证Permission.resource_id必须存在且仅存在于Resource1或Resource2其中一个表中，且不使用中间Resource表，以下是不同数据库的实现方案：

PostgreSQL 实现方案 #

PostgreSQL支持在CHECK约束中使用子查询，同时也支持分区表特性，两种方式都能满足需求：

方式1：CHECK约束结合子查询 #

直接在Permission表上添加CHECK约束，验证resource_id的有效性：

CREATE TABLE User (
    id INT PRIMARY KEY,
    -- 其他业务字段
);

CREATE TABLE Resource1 (
    id INT PRIMARY KEY,
    -- 其他业务字段
);

CREATE TABLE Resource2 (
    id INT PRIMARY KEY,
    -- 其他业务字段
);

CREATE TABLE Permission (
    user_id INT REFERENCES User(id),
    resource_id INT,
    resource_type VARCHAR(20) CHECK (resource_type IN ('resource1', 'resource2')),
    role VARCHAR(20),
    PRIMARY KEY (user_id, resource_id),
    -- 约束：resource_id必须存在于对应类型的资源表
    CONSTRAINT chk_resource_exists CHECK (
        (resource_type = 'resource1' AND EXISTS (SELECT 1 FROM Resource1 WHERE id = resource_id))
        OR
        (resource_type = 'resource2' AND EXISTS (SELECT 1 FROM Resource2 WHERE id = resource_id))
    ),
    -- 可选约束：禁止resource_id同时存在于两个资源表
    CONSTRAINT chk_resource_unique CHECK (
        NOT (
            EXISTS (SELECT 1 FROM Resource1 WHERE id = resource_id)
            AND
            EXISTS (SELECT 1 FROM Resource2 WHERE id = resource_id)
        )
    )
);

这种方式靠约束直接校验，无需额外触发器。如果业务允许同一个resource_id同时出现在两个资源表，可以去掉第二个CHECK约束。

方式2：分区表实现 #

把Permission表按resource_type分成两个分区，每个分区单独设置外键关联对应资源表：

-- 创建父表
CREATE TABLE Permission (
    user_id INT REFERENCES User(id),
    resource_id INT,
    resource_type VARCHAR(20) CHECK (resource_type IN ('resource1', 'resource2')),
    role VARCHAR(20),
    PRIMARY KEY (user_id, resource_id, resource_type)
) PARTITION BY LIST (resource_type);

-- 关联Resource1的分区
CREATE TABLE Permission_resource1 PARTITION OF Permission
FOR VALUES IN ('resource1')
CONSTRAINT fk_perm_res1 FOREIGN KEY (resource_id) REFERENCES Resource1(id);

-- 关联Resource2的分区
CREATE TABLE Permission_resource2 PARTITION OF Permission
FOR VALUES IN ('resource2')
CONSTRAINT fk_perm_res2 FOREIGN KEY (resource_id) REFERENCES Resource2(id);

分区表会自动把不同类型的权限记录分到对应分区，外键约束天然保证resource_id只对应正确的资源表，逻辑更清晰。

SQLite 实现方案 #

SQLite的CHECK约束不支持子查询，所以需要用触发器来实现校验逻辑：

步骤1：创建基础表 #

CREATE TABLE User (
    id INTEGER PRIMARY KEY,
    -- 其他业务字段
);

CREATE TABLE Resource1 (
    id INTEGER PRIMARY KEY,
    -- 其他业务字段
);

CREATE TABLE Resource2 (
    id INTEGER PRIMARY KEY,
    -- 其他业务字段
);

CREATE TABLE Permission (
    user_id INTEGER REFERENCES User(id),
    resource_id INTEGER,
    resource_type TEXT CHECK (resource_type IN ('resource1', 'resource2')),
    role TEXT,
    PRIMARY KEY (user_id, resource_id)
);

步骤2：添加触发器校验 #

-- 插入前校验
CREATE TRIGGER trg_permission_insert_check
BEFORE INSERT ON Permission
FOR EACH ROW
BEGIN
    SELECT CASE
        WHEN NEW.resource_type = 'resource1' AND NOT EXISTS (SELECT 1 FROM Resource1 WHERE id = NEW.resource_id)
            THEN RAISE(ABORT, 'Resource1中不存在该resource_id')
        WHEN NEW.resource_type = 'resource2' AND NOT EXISTS (SELECT 1 FROM Resource2 WHERE id = NEW.resource_id)
            THEN RAISE(ABORT, 'Resource2中不存在该resource_id')
        WHEN EXISTS (SELECT 1 FROM Resource1 WHERE id = NEW.resource_id) AND EXISTS (SELECT 1 FROM Resource2 WHERE id = NEW.resource_id)
            THEN RAISE(ABORT, 'resource_id同时存在于Resource1和Resource2中')
    END;
END;

-- 更新前校验
CREATE TRIGGER trg_permission_update_check
BEFORE UPDATE ON Permission
FOR EACH ROW
BEGIN
    SELECT CASE
        WHEN NEW.resource_type = 'resource1' AND NOT EXISTS (SELECT 1 FROM Resource1 WHERE id = NEW.resource_id)
            THEN RAISE(ABORT, 'Resource1中不存在该resource_id')
        WHEN NEW.resource_type = 'resource2' AND NOT EXISTS (SELECT 1 FROM Resource2 WHERE id = NEW.resource_id)
            THEN RAISE(ABORT, 'Resource2中不存在该resource_id')
        WHEN EXISTS (SELECT 1 FROM Resource1 WHERE id = NEW.resource_id) AND EXISTS (SELECT 1 FROM Resource2 WHERE id = NEW.resource_id)
            THEN RAISE(ABORT, 'resource_id同时存在于Resource1和Resource2中')
    END;
END;

触发器会在插入或更新Permission记录前检查resource_id的有效性，不符合规则就终止操作并抛出提示。如果不需要限制同一个resource_id不能同时出现在两个资源表，可以删掉触发器中对应的CASE分支。

其他数据库（以MySQL为例） #

MySQL 8.0.16之前的版本不支持生效的CHECK约束，所以同样需要用触发器实现：

-- 定义校验函数
DELIMITER //
CREATE FUNCTION check_resource_validity(resource_id INT, resource_type VARCHAR(20))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    IF resource_type = 'resource1' THEN
        RETURN EXISTS (SELECT 1 FROM Resource1 WHERE id = resource_id);
    ELSEIF resource_type = 'resource2' THEN
        RETURN EXISTS (SELECT 1 FROM Resource2 WHERE id = resource_id);
    ELSE
        RETURN FALSE;
    END IF;
END //
DELIMITER ;

-- 插入触发器
DELIMITER //
CREATE TRIGGER trg_permission_insert_check
BEFORE INSERT ON Permission
FOR EACH ROW
BEGIN
    IF NOT check_resource_validity(NEW.resource_id, NEW.resource_type) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'resource_id不存在于对应资源表中';
    END IF;
    IF EXISTS (SELECT 1 FROM Resource1 WHERE id = NEW.resource_id) AND EXISTS (SELECT 1 FROM Resource2 WHERE id = NEW.resource_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'resource_id同时存在于Resource1和Resource2中';
    END IF;
END //
DELIMITER ;

-- 更新触发器
DELIMITER //
CREATE TRIGGER trg_permission_update_check
BEFORE UPDATE ON Permission
FOR EACH ROW
BEGIN
    IF NOT check_resource_validity(NEW.resource_id, NEW.resource_type) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'resource_id不存在于对应资源表中';
    END IF;
    IF EXISTS (SELECT 1 FROM Resource1 WHERE id = NEW.resource_id) AND EXISTS (SELECT 1 FROM Resource2 WHERE id = NEW.resource_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'resource_id同时存在于Resource1和Resource2中';
    END IF;
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者mlisthenewcool