How to have unique index for multiple fields and partial unique index and allow null values?

[simkimsia]

Apologies if I phrase this badly. I'm from the RDBMS world.

In RDBMS such as postgres, I can build a table like this:

• primary autoincrement key called id
• foreignkey to person table called person_id
• foreignkey to product table called product_id
• foreignkey to price table called price_id
• boolean field called is_removed

1. If i want a unique constraint such as the entire table can have a unique index such that (product_id, price_id) are together as a pair must be unique, I can do that. Is this possible for Agensgraph?

2. if i want a partial unique constraint in postgres where if the is_removed is False, then the person_id, product_id, and price_id are unique. I can do that in postgres. Is this possible for Agensgraph?

3. Then if any of the foreignkeys are null, the unique constraints are not triggered. Is this possible in Agensgraph?

[protodef]

I think the below example queries answer your questions.

--
-- prepare for the test
--

-- create a test graph
CREATE GRAPH g;

-- set the current graph_path to g
SET graph_path = g;

--
-- 1. unique
--

-- create a vertex:product that has product_id and price_id
CREATE (:product {product_id: 'product-1', price_id: 'price-1'});

-- create a unique index over product (product_id, price_id)
CREATE UNIQUE PROPERTY INDEX ON product (product_id, price_id);

-- try to create a vertex:product that has the same product_id and price_id
-- (should fail)
CREATE (:product {product_id: 'product-1', price_id: 'price-1'});

-- create a vertex:product that has different id and name
-- (should succeed)
CREATE (:product {product_id: 'product-2', price_id: 'price-1'});

--
-- 2. unique partial
--

-- create a vertex:purchase that has
-- person_id, product_id, price_id, and is_removed=true
CREATE (:purchase {person_id: 'person-1', product_id: 'product-1', price_id: 'price-1',
                   is_removed: true});

-- create a unique partial index over purchase (person_id, product_id, price_id)
CREATE UNIQUE PROPERTY INDEX ON purchase (person_id, product_id, price_id)
  WHERE is_removed = jsonb_in('false');

-- try to create the same vertex again
-- (should succeed)
CREATE (:purchase {person_id: 'person-1', product_id: 'product-1', price_id: 'price-1',
                   is_removed: true});

-- create a vertex:purchase that has the same person_id, product_id, and price_id
-- but is_removed=false
-- (should succeed)
CREATE (:purchase {person_id: 'person-1', product_id: 'product-1', price_id: 'price-1',
                   is_removed: false});

-- try to create the same vertex again
-- (should fail)
CREATE (:purchase {person_id: 'person-1', product_id: 'product-1', price_id: 'price-1',
                   is_removed: false});

-- check the query plan
SET enable_seqscan = off;
-- index scan
EXPLAIN MATCH (n:purchase) WHERE n.is_removed = jsonb_in('false')
        RETURN n;
-- sequential scan
EXPLAIN MATCH (n:purchase)
        RETURN n;

--
-- 3. unique partial with null values
--

-- create a complete vertex:purchase
CREATE (:purchase {person_id: 'person-2', product_id: 'product-2', price_id: 'price-2',
                   is_removed: false});

-- create a vertex:purchase that has null price_id
CREATE (:purchase {person_id: 'person-2', product_id: 'product-2', price_id: null,
                   is_removed: false});

-- create a vertex:purchase that has null values for the properties
CREATE (:purchase {person_id: null, product_id: null, price_id: null,
                   is_removed: false});

-- create the same vertex:purchase
-- (should succeed)
CREATE (:purchase {person_id: null, product_id: null, price_id: null,
                   is_removed: false});

-- retrieve the vertices
MATCH (n:purchase) WHERE n.is_removed = jsonb_in('false')
RETURN n;

--
-- clean up
--

SET enable_seqscan = on;
DROP GRAPH g CASCADE;

[simkimsia]

Thanks for the detailed reply. Will try it out on your docker image