Cypher - Conditional Create

Question

How Normal Create Works in a Database

In most create circumstances, you don't want to create a new node if one of the unique or primary keys already exist in the database. I also don't want to update the node, as that would be an upsert. I want to do nothing if the key exists in the database. This will work just like a unique constraint.

Create with a Unique Key

If I want only the email field to be unique, I can do this:

MERGE (u:User { email: '[email protected]' })
ON CREATE SET u.name='Jon Smith'
RETURN u

It won't create the user if there is already an email.

Create with Multiple Unique Keys ?

Let's say I don't want the user to be able to create a user where a username or email is already in the database. You would think this would work:

MERGE (u:User { email: '[email protected]', username: 'bill' })
ON CREATE SET u.name='Jon Smith'
RETURN u

However, this will still create the node, as only the COMBINATION has to be unique. I want both values separately to be unique... or fail.

Conditional Create

What I want is a conditional create: IF X THEN Y where:

x = email OR username IS NOT IN User
y = CREATE User email="[email protected]", username="bill", role="admin", ...

How can this be done?

J

Answer 1

Please consider the following query:

GRAPH.QUERY g "cypher email='a' username='b'
OPTIONAL MATCH (n)
WHERE n.email = $email OR n.username = $username
WITH n is null as missing
WHERE missing = true
CREATE ({email:$email, username:$username})"

Given an empty graph:

GRAPH.QUERY g "cypher email='a' username='b' OPTIONAL MATCH (n) WHERE n.email = $email OR n.username = $username  WITH n is null as missing WHERE missing = true CREATE (n {email:$email, username:$username})"
1) 1) "Nodes created: 1"
   2) "Properties set: 2"
   3) "Cached execution: 0"
   4) "Query internal execution time: 1.469000 milliseconds"

GRAPH.QUERY g "cypher email='a' username='b' OPTIONAL MATCH (n) WHERE n.email = $email OR n.username = $username  WITH n is null as missing WHERE missing = true CREATE (n {email:$email, username:$username})"
    1) 1) "Cached execution: 1"
       2) "Query internal execution time: 0.614000 milliseconds"

Running the exact same query twice, the first execution created the missing node the second one didn't modified the graph.