sql - ON DELETE 外键引用的条件逻辑

Question

我希望能够为外键 ON DELETE 约束执行一些逻辑。根据逻辑，我要么想要级联，要么想要限制。

我的表:

CREATE TABLE users (
  user_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY
);

CREATE TABLE teams (
  team_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY
);

CREATE TABLE documents (
  document_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY
  user_id uuid REFERENCES users ON DELETE /*do logic here*/,
  team_id uuid REFERENCES teams ON DELETE /*do logic here*/,
  content text
);

在我的(非常基本的)表格中，文档 可以由用户 和/或团队 拥有。

关于文档，如果有人删除文档的用户并且文档的team_id IS NULL 然后将删除级联到文档(或者反之亦然与 user_id 和 team_id ).

否则，如果有人删除文档的用户并且文档的 team_id 不为空，则限制删除用户(或反之亦然，使用 user_id 和 team_id)。

我没有在文档中看到任何实现此目的的方法。我是否需要在我的服务器代码中而不是在数据库中执行此逻辑？我想要额外的数据完整性层。

Answer 1

很有可能，但不是直接的。你需要一个触发器。先设置外键为RESTRICT

CREATE TABLE documents (
  document_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id uuid REFERENCES users ON DELETE RESTRICT,
  team_id uuid REFERENCES teams ON DELETE RESTRICT,
  content text
);

然后为用户表创建触发器函数和触发器

CREATE OR REPLACE FUNCTION user_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM documents WHERE team_id IS NULL and user_id = OLD.user_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_delete_trigger BEFORE DELETE ON users FOR EACH ROW     EXECUTE PROCEDURE user_delete();

然后对团队表执行相同的操作。

CREATE OR REPLACE FUNCTION team_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM documents WHERE user_id IS NULL and team_id = OLD.team_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER team_delete_trigger BEFORE DELETE ON users FOR EACH ROW     EXECUTE PROCEDURE team_delete();

我相信这正是您正在寻找的。