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
CodePudding user response:
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.