单列关联两张不同表：actions表建模与外键约束配置问题

现有users和organizations两张表，需要创建新表actions记录针对用户或组织的操作，考虑两种建模方案：

• 方案一：新增type列，同时添加分别对应两张表ID的user_id、org_id列，但会产生稀疏数据（某一类型操作时另一ID列值为NULL）
• 方案二：使用type列加通用entity_id列的方案，但配置双外键时触发报错

测试SQL代码：

CREATE TABLE users (user_id SERIAL PRIMARY KEY);

CREATE TABLE organizations (org_id SERIAL PRIMARY KEY);

CREATE TABLE actions (
    action_id SERIAL PRIMARY KEY,
    type VARCHAR(10) NOT NULL,
    entity_id INT NOT NULL,
    FOREIGN KEY (entity_id) REFERENCES users (user_id),
    FOREIGN KEY (entity_id) REFERENCES organizations (org_id)
  );

INSERT INTO users DEFAULT VALUES;
INSERT INTO users DEFAULT VALUES;

INSERT INTO organizations DEFAULT VALUES;

INSERT INTO actions (type, entity_id) VALUES ('user', 2);

错误信息：

postgres=# INSERT INTO actions (type, entity_id) VALUES ('user', 2);
ERROR:  insert or update on table "actions" violates foreign key constraint "actions_entity_id_fkey1"
DETAIL:  Key (entity_id)=(2) is not present in table "organizations".

方法1：使用PostgreSQL的部分外键约束（推荐） #

PostgreSQL 12及以上支持部分外键，可通过WHERE子句让外键约束仅在特定条件下生效，完美匹配需求：

CREATE TABLE users (user_id SERIAL PRIMARY KEY);

CREATE TABLE organizations (org_id SERIAL PRIMARY KEY);

CREATE TABLE actions (
    action_id SERIAL PRIMARY KEY,
    type VARCHAR(10) NOT NULL CHECK (type IN ('user', 'org')), -- 限制type的合法值
    entity_id INT NOT NULL,
    -- 当type为'user'时，entity_id必须存在于users表
    FOREIGN KEY (entity_id) REFERENCES users (user_id)
        DEFERRABLE INITIALLY DEFERRED
        WHERE (type = 'user'),
    -- 当type为'org'时，entity_id必须存在于organizations表
    FOREIGN KEY (entity_id) REFERENCES organizations (org_id)
        DEFERRABLE INITIALLY DEFERRED
        WHERE (type = 'org')
);

-- 测试插入用户操作（正常执行）
INSERT INTO users DEFAULT VALUES;
INSERT INTO users DEFAULT VALUES;
INSERT INTO actions (type, entity_id) VALUES ('user', 2);

-- 测试插入组织操作（正常执行）
INSERT INTO organizations DEFAULT VALUES;
INSERT INTO actions (type, entity_id) VALUES ('org', 1);

-- 测试非法插入（会报错，因为type是user但entity_id不在users中）
-- INSERT INTO actions (type, entity_id) VALUES ('user', 99);

方法2：检查约束+触发器 #

如果你的PostgreSQL版本低于12，无法使用部分外键，可以用检查约束配合触发器实现：

1. 创建actions表并添加type的检查约束：

CREATE TABLE actions (
    action_id SERIAL PRIMARY KEY,
    type VARCHAR(10) NOT NULL CHECK (type IN ('user', 'org')),
    entity_id INT NOT NULL
);

2. 创建触发器函数，验证entity_id是否匹配对应表：

CREATE OR REPLACE FUNCTION validate_action_entity()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.type = 'user' THEN
        IF NOT EXISTS (SELECT 1 FROM users WHERE user_id = NEW.entity_id) THEN
            RAISE EXCEPTION '用户ID % 不存在', NEW.entity_id;
        END IF;
    ELSIF NEW.type = 'org' THEN
        IF NOT EXISTS (SELECT 1 FROM organizations WHERE org_id = NEW.entity_id) THEN
            RAISE EXCEPTION '组织ID % 不存在', NEW.entity_id;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 为actions表绑定触发器：

CREATE TRIGGER trigger_validate_action_entity
BEFORE INSERT OR UPDATE ON actions
FOR EACH ROW EXECUTE FUNCTION validate_action_entity();

方法3：回到稀疏列方案 #

如果不想用复杂约束，也可以接受稀疏数据，采用方案一的建模方式，通过检查约束确保同一时间只有一个ID列有值：

CREATE TABLE actions (
    action_id SERIAL PRIMARY KEY,
    type VARCHAR(10) NOT NULL CHECK (type IN ('user', 'org')),
    user_id INT REFERENCES users(user_id),
    org_id INT REFERENCES organizations(org_id),
    -- 确保type和对应ID列匹配，且只有一个ID非空
    CHECK (
        (type = 'user' AND user_id IS NOT NULL AND org_id IS NULL)
        OR (type = 'org' AND org_id IS NOT NULL AND user_id IS NULL)
    )
);

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