How to create a unique index with conditional and subquery in PostgreSQL?

Question

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.

I get the following error:

ERROR:  cannot use subquery in index predicate

Is there any way to solve this problem?

The above model Represents the situation of the case.

• The client Can Be either an ordinary person, or a company
• If an ordinary person is, she will not have FK in "legal_entity" table.
• If an ordinary person is, she Should only one record in "user_client" table.

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

Script generate tables:

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

Answer 1

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

Answer 2

I solved my problem by adding a rule table:

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;