问题:如何在PostgreSQL中用条件和子查询创建唯一索引?

我使用 PGSQL 并尝试在下面添加索引。

CREATE UNIQUE INDEX fk_client ON user_client (fk_client) WHERE fk_client NOT IN(SELECT fk_client FROM legal_entity);

但是......这是不可能的,因为在创建索引时允许运行子查询。

我收到以下错误:

ERROR:  cannot use subquery in index predicate

有没有办法解决这个问题?

上面的模型代表了案例的情况。

• 客户可以是普通人,也可以是公司

• 一个普通人是,她不会在“legal\entity”表中有FKA。

• 如果是普通人,她应该在“user_client”表中只有一条记录。

用索引没有,但是有什么办法可以解决这个问题吗?...

脚本生成表:

-- user is a special word, then renamed to users
CREATE TABLE users (
    id_user INT,
    name VARCHAR(50) NOT NULL,
    CONSTRAINT user_pkey PRIMARY KEY (id_user)
);

CREATE TABLE client (
    id_client INT,
    CONSTRAINT client_pkey PRIMARY KEY (id_client)
);

CREATE TABLE legal_entity (
    fk_client INT,
    federal_id VARCHAR(14) NOT NULL,
    CONSTRAINT legal_entity_pkey PRIMARY KEY (fk_client),
    CONSTRAINT legal_entity_fkey FOREIGN KEY (fk_client) REFERENCES client (id_client)
);

CREATE TABLE user_client (
    fk_client INT,
    fk_user INT,
    CONSTRAINT user_client_pkey PRIMARY KEY (fk_client, fk_user),
    CONSTRAINT user_client_fkey_1 FOREIGN KEY (fk_client) REFERENCES client (id_client),
    CONSTRAINT user_client_fkey_2 FOREIGN KEY (fk_user) REFERENCES users (id_user)
);

解答

使用规则的缺点是规则只是在解析查询后重写查询,因此如果通过触发器添加数据,则不会触发。添加一个使用您的逻辑调用函数的 CHECK 约束会更安全。如果我正确地遵循了你的逻辑,它应该是这样的:

CREATE OR REPLACE FUNCTION check_user_client(fkc int) 
  RETURNS boolean AS
$$
DECLARE
  i int;
BEGIN
  SELECT count(*) INTO i FROM legal_entity WHERE fk_client = fkc;
  IF (i > 0) THEN
    RETURN true;
  END IF;

  SELECT count(*) INTO i FROM user_client WHERE fk_client = fkc;
  IF (i = 0) THEN
    RETURN true;
  END IF;

  RETURN false;  
END
$$ LANGUAGE plpgsql;

ALTER TABLE user_client ADD CONSTRAINT unique_user CHECK (check_user_client(fk_client));