如何在PostgreSQL中使用条件和子查询创建唯一的索引?[英] How to create a unique index with conditional and subquery in PostgreSQL?

本文是小编为大家收集整理的关于如何在PostgreSQL中使用条件和子查询创建唯一的索引?的处理方法,想解了如何在PostgreSQL中使用条件和子查询创建唯一的索引?的问题怎么解决?如何在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"表中拥有FK.
  • 如果一个普通人是,她只能在" 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)
);

推荐答案

使用规则的缺点是,将查询解析后,只需重写查询,因此如果通过触发添加数据,则不会触发.添加使用逻辑调用函数的检查约束是更安全的.如果我正确遵循您的逻辑,则应该是:

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));

其他推荐答案

我通过添加规则表解决了我的问题:

CREATE OR REPLACE RULE rule_test AS ON INSERT
    TO user_client WHERE (
        (SELECT fk_client FROM legal_entity WHERE fk_client = new.fk_client) IS NULL) AND (
        (SELECT fk_client FROM user_client WHERE fk_client = new.fk_client) IS NOT NULL)
    DO INSTEAD NOTHING;

其他推荐答案

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE foo
        ( id INTEGER NOT NULL PRIMARY KEY
        );
CREATE TABLE tbl_relation
        ( id INTEGER NOT NULL PRIMARY KEY
        , foo_id INTEGER REFERENCES foo(id)
        , fk_1 INTEGER
        );

CREATE UNIQUE INDEX fk_1 ON tbl_relation (fk_1)
        WHERE foo_id IS NULL;

更新:这是数据模型修改后的新情况. 关键是:Legal_entity和客户似乎共享一个关键领域(DOE看不正确),将其自身的关键域名授予Legal_entity.

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

-- user is a special word, then renamed to users
CREATE TABLE users
        ( id INTEGER PRIMARY KEY
        -- name is a special word
        , zname VARCHAR(50) NOT NULL
        );

CREATE TABLE client
        (id INTEGER PRIMARY KEY
        );

CREATE TABLE legal_entity
        ( id INTEGER PRIMARY KEY
        , client_id INTEGER REFERENCES client(id)
        , federal_id VARCHAR(14) NOT NULL
        );

CREATE TABLE user_client
        ( client_id INTEGER REFERENCES client (id)
        , user_id INTEGER REFERENCES users (id)
        , legal_id INTEGER REFERENCES legal_entity(id)
        , CONSTRAINT user_client_pkey PRIMARY KEY (client_id, user_id) 
        );

CREATE INDEX tres_stupide ON user_client (client_id) WHERE legal_id IS NULL;

本文地址:https://www.itbaoku.cn/post/1764133.html