sql - 具有非零基数的多对多关系

Question

假设我有一个包含表 A、B 和 C 的 PostgreSQL 数据库，其中 A 和 B 通过联结表 C 具有多对多关系。这些表具有以下 SQL 定义:

CREATE TABLE A
( 
  id serial NOT NULL,
  CONSTRAINT A_pkey PRIMARY KEY (id)
)

CREATE TABLE B
(
  id serial NOT NULL,
  CONSTRAINT B_pkey PRIMARY KEY (id)
)

CREATE TABLE C
(
  A_id integer NOT NULL,
  B_id integer NOT NULL,
  CONSTRAINT C_pk PRIMARY KEY (A_id, B_id),
  CONSTRAINT A_fk FOREIGN KEY (A_id)
      REFERENCES A(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT B_fk FOREIGN KEY (B_id)
      REFERENCES B(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

现在我想确保对于 A 的每个实例至少有一个 B 实例(换句话说:A 中的每个 id 至少作为 C 中的 A_id 出现一次)。是否可以使用数据库约束检查此属性？

Answer 1

满足您要求的更好方法是约束触发器。

它应该对表 C 执行 exists() 检查，并在所需键不存在时引发异常。 (从技术上讲，这就是强制执行外键约束的内置触发器的基本功能。)

它会是这样的:

create function A_C_fkey_check() returns trigger as $$
begin
  if not exists (select 1 from C where A_id = new.id) then
    raise exception 'invalid key';
  end if;
  return null;
end;
$$ language plpgsql;

create constraint trigger A_C_fkey_check after insert on A
for each row execute procedure A_C_fkey_check();

也就是说，我建议在数据库级别实际执行这种类型的约束并不是一个好主意:在我自己的经验中，总是会出现需要 A 而没有 B 的奇怪边缘情况。