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?

enter image description here

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

Please give an actual table definition instead of a textual description. — wildplasser, Aug 01 '12 at 19:42
Great! Now edited the question and added scripts to create tables. — FabianoLothor, Aug 01 '12 at 20:13
That's better. The change in your model could possibly invalidate my answer. Why didn't you add a complete model to your initial Q? That would have saved us from the guesswork and the typing. — wildplasser, Aug 01 '12 at 20:16
Your answer may also be edited in addition the model is the same, the difference is that now there is a context. — FabianoLothor, Aug 01 '12 at 20:21

score 4 · Accepted Answer · answered Aug 02 '12 at 15:16

4

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

answered Aug 02 '12 at 15:16

Jim

761
4
4

Thank's! I will test when I have time. – FabianoLothor Aug 02 '12 at 17:58
Thank you! The function works perfectly, sorry for the delay in giving feedback – FabianoLothor Aug 06 '12 at 11:41
1

How do you deal with concurrent transactions here? They may all satisfy the condition and will then commit, putting the table in inconsistent state. – Pawel Zieminski Sep 17 '19 at 00:46

score 1 · Answer 2 · answered Aug 02 '12 at 12:02

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;

wildplasser · Answer 3 · 2012-08-01T20:52:44.573

-2

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;

UPDATE: this is the new situation after the modification of the data-model. The point is: legal_entity and client seem to share a key domain (which doe not seen right) Giving legal_entity its own key-domain seems more appropiate.

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;

edited Aug 01 '12 at 20:52

answered Aug 01 '12 at 19:30

wildplasser

43,142
8
66
109

foo_id can not be null fk_1 and fk_2 PKs are the tbl_relation – FabianoLothor Aug 01 '12 at 19:33
Foo_id is a foreign key and can be null. fk_1 is conditional on (foo_id not being NULL). And I don't understand the rest of your sentence. Maybe the confusion about the OP calling a columns fk_1, which is a very bad name for a column. – wildplasser Aug 01 '12 at 19:38
I edited the question, I believe that now is best to understand. – FabianoLothor Aug 01 '12 at 20:01
client_id in legal entity is PK, no sense insert duplicate values in user_client. – FabianoLothor Aug 02 '12 at 11:14
Your data model just does not seem right. the user_client table allows an N::M relationship between user and client. In the text you explain that a client is either a "natural" person or a legal entity. The N::M relation will allow a client to be a collection of persons, and a s to belong to multiple of these collections. The occurrence of client_id as a PK in more than one table is also suspect, IMO. – wildplasser Aug 02 '12 at 11:24
Suspect? This is called specialization. The problem is that a client can have multiple users, however if an ordinary person should only have one. – FabianoLothor Aug 02 '12 at 11:28

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

3 Answers3

Linked