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

问题描述

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

I use PGSQL and try add the index below.

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

但是...不可能，因为允许创建子查询

But ... It is not possible, because there is allowed run a sub query in the creation of an index.

我收到以下错误：

ERROR:  cannot use subquery in index predicate

中使用子查询？

上述模型代表案件的情况。

The above model Represents the situation of the case.

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


• 如果是普通人，则她在 legal_entity表中不会有FK。


• 如果是普通人，则她在 user_client表。

没有索引，但是有什么方法可以解决此问题？...

With index does not, but is there any way to solve this problem?...

脚本生成表：

-- 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约束更为安全。如果我正确地遵循了您的逻辑，则应该是这样：

The downside to using a rules is that rules simply rewrite the query after it is parsed so if the data is added through a trigger, it will not fire. It's safer to add a CHECK constraint that calls a function with your logic. If I follow your logic correctly, it should be something like:

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

这篇关于如何在PostgreSQL中使用条件查询和子查询创建唯一索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！