14

I am preplexed on why I am getting an issue with this Cypher statment when I have a unique constraint on the address of the location node but am using a merge which should find that if it exists and only return the id for the rest of the statment. What am I missing?

Here is my statement:

MERGE(l:Location{location_name:"Starbucks", address:"36350 Van Dyke Ave", city: "Sterling Heights",state: "MI", zip_code:"48312",type:"location",room_number:"",long:-83.028889,lat:42.561152})
CREATE(m:Meetup{meet_date:1455984000,access:"Private",status:"Active",type:"project",did_happen:"",topic:"New features for StudyUup",agenda:"This is a brainstorming session to come with with new ideas for the companion website, StudyUup. Using MatchUup as the base, what should be added, removed, or modified? Bring your thinking caps and ideas!"})
WITH m,l 
MATCH (g:Project{title_slug:"studyuup"}) MATCH (p:Person{username:"wkolcz"})
WITH m,l,g,p  
MERGE (g)-[:CREATED {rating:0}]->(m)
MERGE (m)-[:MEETUP_AT {rating:0}]->(l)-[:HOSTED_MEETUP]->(m)
MERGE (m)<-[:ATTENDING]-(p)
RETURN id(m) as meeting_id

I am getting:

Node 416 already exists with label Location and property "address"=[36350 Van Dyke Ave]
Wally Kolcz
  • 1,604
  • 3
  • 24
  • 45

1 Answers1

39

You've encountered a common misunderstanding of MERGE. MERGE merges on everything you've specified within the single MERGE clause. So the order of operations are:

  1. Search for a :Location node with all of the properties you've specified.
  2. If found, return the node.
  3. If not found, create the node.

Your problem occurs at step 3. Because a node with all of the properties you've specified does not exist, it goes to step 3 and tries to create a node with all of those properties. That's when your uniqueness constraint is violated.

The best practice is to merge on the property that you've constrained to be unique and then use SET to update the other properties. In your case:

MERGE (l:Location {address:"36350 Van Dyke Ave"})
SET l.location_name = "Starbucks",
     l.city = "Sterling Heights"
...

The same logic is going to apply for the relationships you're merging later in the query. If the entire pattern doesn't exist, it's going to try to create the entire pattern. That's why you should stick to the best practice of:

MERGE (node1:Label1 {unique_property: "value"})
MERGE (node2:Label2 {unique_property: "value"})
MERGE (node1)-[:REL]-(node2)
Nicole White
  • 7,720
  • 29
  • 31
  • 2
    Hey Nicole! Thanks for the insight. I realized that I must have added an additional attribute (or removed one) that is causing only a partial match that is then causing the conflict with the constraint. Thanks! – Wally Kolcz Feb 13 '16 at 17:54
  • 2
    This is a super helpful answer, which I found after trying a `LOAD CSV` that complained. – vielmetti Aug 28 '16 at 02:33
  • I don't understand what is said here... I try to do something exactly like the last 3 lines... I was hoping a unique `node1` would be created, but I end up with many `node1`'s. What I want: create `node1` if it doesn't exist, create node2 (which I know does not exist but I use MERGE anyway), then create a (necessarily new) link between the 2. – Yan King Yin Apr 01 '21 at 13:08
  • PS: even if I use a `MATCH node, MATCH node, MERGE link` pattern I still get duplicates of `node1`... – Yan King Yin Apr 01 '21 at 14:08
  • Oh, my bad. The `MERGE-MERGE-MERGE` pattern actually works, but my database was unclean. After I deleted previous nodes, it's OK. – Yan King Yin Apr 01 '21 at 14:25