Neo4j is creating multiple nodes and relationships with the same cypher

Question

I'm trying to dynamically generate cyphers to create nodes and relationships on Neo4j (v3.0.4) but I'm getting some weird results.

I've been working with cypher queries for a while now and I can't see what's wrong with my query here

So I have a Neo4j database with a constraint on unique id for :Individuals

CREATE CONSTRAINT ON (i:Individual) ASSERT i.id IS UNIQUE

Given that, I'm running the following cypher query:

MERGE (parent:Individual {id:"334717eb182371a126e46d44bde3ef6b"}) 
SET parent.name = "SOME PARENT NAME"
WITH parent 
OPTIONAL MATCH (parent)<-[del:IS_RELATIVE]-(n)

WITH parent, n, del 
DELETE del 
WITH parent 

CREATE (c1:Individual {name:"CHILD 1"}) 
CREATE (parent)<-[r1:IS_RELATIVE {birth:"2017-02-24"}]-(c1) 

CREATE (c2:Individual {name:"CHILD 2"}) 
CREATE (parent)<-[r2:IS_RELATIVE {birth:"2015-01-23"}]-(c2)

And the first time I run it, it results me:

Added 3 labels, created 3 nodes, set 7 properties, created 2 relationships, statement executed in 1201 ms

Which is great! The expected result.
But if I run the same query again, it results

Added 4 labels, created 4 nodes, set 11 properties, deleted 2 relationships, created 4 relationships, statement executed in 540 ms.

And if I run the same query again:

Added 8 labels, created 8 nodes, set 21 properties, deleted 4 relationships, created 8 relationships, statement executed in 192 ms.

And then

Added 16 labels, created 16 nodes, set 41 properties, deleted 8 relationships, created 16 relationships, statement executed in 583 ms.

And notice that the first node is not being duplicated, just the "child" ones with their relationships...

I don't know what I'm missing...
Thanks

Answer 1

I believe that the more simple way to fix your query is changing all CREATE statements by MERGE, like this:

MERGE (parent:Individual {id:"334717eb182371a126e46d44bde3ef6b"}) 
SET parent.name = "SOME PARENT NAME"
WITH parent 
OPTIONAL MATCH (parent)<-[del:HAS_CHILDREN]-(n)

WITH parent, n, del 
DELETE del 
WITH parent 

MERGE (c1:Individual {name:"CHILD 1"}) 
MERGE (parent)<-[r1:HAS_CHILDREN {birth:"2017-02-24"}]-(c1) 

MERGE (c2:Individual {name:"CHILD 2"}) 
MERGE (parent)<-[r2:HAS_CHILDREN {birth:"2015-01-23"}]-(c2)

This way you can run the above query multiple times and the nodes will not be created again.

Answer 2

You only have a uniqueness constraint is for the id property of Individual nodes.

Because there is no uniqueness constraint on the name property, your CREATE (c1:Individual {name: "CHILD x"}) clauses will always create new nodes. You would need to specify a id property when you create those nodes in order to see constraint errors whenever you tried to re-use an existing id.

Also, you should use MERGE instead of CREATE so that you don't have to deal with errors when the constraint is violated, and so that your query is not aborted.

For example:

MERGE (parent:Individual {id:"334717eb182371a126e46d44bde3ef6b"}) 
SET parent.name = "SOME PARENT NAME"
WITH parent 
OPTIONAL MATCH (parent)<-[del:IS_RELATIVE]-()

DELETE del 
WITH parent 

MERGE (c1:Individual {id: "1234567890"})
SET c1.name = "CHILD 1"
CREATE (parent)<-[r1:IS_RELATIVE {birth:"2017-02-24"}]-(c1) 

MERGE (c2:Individual {id: "2345678901"}) 
SET c2.name = "CHILD 2"
CREATE (parent)<-[r2:IS_RELATIVE {birth:"2015-01-23"}]-(c2)

In case a child's node already existed (with the same id), but it did not have the name you want to assign to it, the above query sets the name in a separate SET clause. (If you kept the name property in the MERGE clause, you would get an error if a node with the same id already existed, but it had a different name.)

NOTE: you probably also need to think about what to do if a child already has IS_RELATIVE relationships that are no longer appropriate.