PostgreSQL：影响多个表的约束

问题描述

我有一个实体的层次结构，每个实体可能有多个名称，因此我有一个单独的名称表。简化的SQL模式如下所示：

I have a hierarchical structure of entities, each of which might have more than one name, so that I have a separate table for names. A simplified SQL Schema looks as follows:

CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  -- some fields
);

CREATE TABLE entities (
  id SERIAL NOT NULL PRIMARY KEY,
  parent_id INTEGER NOT NULL,
  owner_id INTEGER NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES entities(id),
  FOREIGN KEY (owner_id) REFERENCES users(id)
);

CREATE TABLE entity_names (
  id SERIAL NOT NULL PRIMARY KEY,
  entity_id INTEGER NOT NULL,
  name VARCHAR(30) NOT NULL,
  FOREIGN KEY (entity_id) REFERENCES entities(id)
);

而且我需要确保没有名称重复的实体（当然，相同的 parent_id 和 owner_id ）。

And I need to ensure that there's no entities with duplicate name (of course, with the same parent_id and owner_id).

我可以确保在使用以下查询向 entity_names 添加名称：

I can ensure that right before adding a name to entity_names with the query like this:

SELECT COUNT(t.id)
  FROM entity_names n
  JOIN entities e ON n.entity_id = e.id
  WHERE e.parent_id = 123 and e.owner_id = 456 and n.name = 'foo'

但是我想知道是否有可能（并且理智地）在数据库中实现此约束？

But I'm wondering if it's possible (and sane) to implement this constraint in the database?

推荐答案

Postgres中没有多表约束，但是您可以模拟 check 通过将查询放在 tag_names 的触发器中来约束。

There is no multitable constraints in Postgres but you can emulate check constraint by placing your query in a trigger on tag_names.

这篇关于PostgreSQL：影响多个表的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！