主页 / dba / 问题 / 89534
Accepted
lpd
Asked: 2015-01-18 03:50:53 +0800 CST

跨postgres中的多个表的部分唯一约束

  • 772

为了在 postgres 中强制执行部分唯一性，创建部分唯一索引而不是显式约束是一种众所周知的解决方法，如下所示：

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id),
  special boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX possession_unique_special ON possession(owner_id, special) WHERE special = true;

这将限制每个所有者在数据库级别拥有不超过一个特殊财产。显然不可能创建跨越多个表的索引，因此该方法不能用于在列存在于不同表中的超类型和子类型情况下强制执行部分唯一性。

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id)
);

CREATE TABLE toy (
  possession_id integer PRIMARY KEY REFERENCES possession(possession_id),
  special boolean NOT NULL DEFAULT false
);

如您所见，在此示例中，较早的方法不允许将每个所有者限制为不超过一个特殊玩具。假设每个财产都必须实现一个子类型，那么在 postgres 中强制执行此约束而又不显着改变原始表的最佳方法是什么？

postgresql inheritance

2 个回答

  1. 不是 postgres 的答案，但对于偶然发现此问题的使用 Oracle 的人可能有用：

    Oracle 允许使用快速刷新的物化视图跨多个表强制执行部分唯一性。汤姆凯特在这里描述了它。简而言之，如果连接在提交时产生任何行，则它违反了对物化视图的约束。

    未经测试，但原则上它应该像这样工作：

    create materialized view log
  on possession with rowid;

create materialized view log
  on toy with rowid;

create materialized view one_special_toy_per_owner
  refresh fast
  on commit
  as select p.owner_id, count(t.special) as special
     from possession p, toy t
     where p.possession_id = t.possession_id
     group by p.owner_id
     having count(t.special) > 1;

alter table one_special_toy_per_owner
  add constraint owner_has_only_one_special_toy
  check (owner_id is null and special is null);
    • 2
  2. Best Answer

    Postgres 直接支持表继承，这可以解决问题：

    CREATE TABLE base_possession (
  possession_id SERIAL PRIMARY KEY
);

CREATE TABLE possession (
  possession_id INTEGER NOT NULL REFERENCES base_possession(possession_id),
  owner_id INTEGER NOT NULL REFERENCES owner(owner_id)
);

CREATE TABLE toy (
  special boolean NOT NULL DEFAULT false,
  PRIMARY KEY (possession_id),
  FOREIGN KEY (possession_id) REFERENCES base_possession(possession_id),
  FOREIGN KEY (owner_id) REFERENCES owner(owner_id)
) INHERITS (possession);
CREATE UNIQUE INDEX toy_unique_special ON toy(owner_id, special) WHERE special = true;
    • 2

相关问题