Cypher, create unique relationship for one node

Question

I want to add a "created by" relationship on nodes in my database. Any node should be able of having this relationship but there can never be more than one.

Right now my query looks something like this:

MATCH (u:User {email: '[email protected]'})
MERGE (n:Node {name: 'Node name'})
ON CREATE SET n.name='Node name', n.attribute='value'
CREATE UNIQUE (n)-[:CREATED_BY {date: '2015-02-23'}]->(u)
RETURN n

As I have understood Cypher there is no way to achieve what I want, the current query will only make sure there are no unique relationships based on TWO nodes, not ONE. So, this will create more CREATED_BY relationships when run for another User and I want to limit the outgoing CREATED_BY relationship to just one for all nodes.

Is there a way to achieve this without running multiple queries involving program logic?

Thanks.

Update

I tried to simplyfy the query by removing implementation details, if it helps here's the updated query based on cybersams response.

MERGE (c:Company {name: 'Test Company'}) 
ON CREATE SET c.uuid='db764628-5695-40ee-92a7-6b750854ebfa', c.created_at='2015-02-23 23:08:15', c.updated_at='2015-02-23 23:08:15' 
WITH c
OPTIONAL MATCH (c)
WHERE NOT (c)-[:CREATED_BY]-() 
CREATE (c)-[:CREATED_BY {date: '2015-02-23 23:08:15'}]->(u:User {token: '32ba9d2a2367131cecc53c310cfcdd62413bf18e8048c496ea69257822c0ee53'})  
RETURN c

Still not working as expected.

Update #2

I ended up splitting this into two queries.

The problem I found was that there was two possible outcomes as I noticed.

The CREATED_BY relationship was created and (n) was returned using OPTIONAL MATCH, this relationship would always be created if it didn't already exist between (n) and (u), so when changing the email attribute it would re-create the relationship.
The Node (n) was not found (because of not using OPTIONAL MATCH and the WHERE NOT (c)-[:CREATED_BY]-() clause), resulting in no relationship created (yay!) but without getting the (n) back the MERGE query looses all it's meaning I think.

My Solution was the following two queries:

MERGE (n:Node {name: 'Name'})
ON CREATE SET
SET n.attribute='value'
WITH n
OPTIONAL MATCH (n)-[r:CREATED_BY]-()
RETURN c, r

Then I had program logic check the value of r, if there was no relationship I would run the second query.

MATCH (n:Node {name: 'Name'})
MATCH (u:User {email: '[email protected]'})
CREATE UNIQUE (n)-[:CREATED_BY {date: '2015-02-23'}]->(u)
RETURN n

Unfortunately I couldn't find any real solution to combining this in one single query with Cypher. Sam, thanks! I've selected your answer even though it didn't quite solve my problem, but it was very close.

I think you want to remove the OPTIONAL part otherwise the CREAT always fires and always created the user node. — Dave Bennett
You're right Dave, of course. Problem is I also always want to return the (n) regardless of if the CREATE_BY relationship was created or not. I'm starting to think it's impossible to make a query like that. — Andreas

cybersam cybersam · Accepted Answer · 2015-02-23T22:40:24

This should work for you:

MERGE (n:Node {name: 'Node name'})
ON CREATE SET n.attribute='value'
WITH n
OPTIONAL MATCH (n)
WHERE NOT (n)-[:CREATED_BY]->()
CREATE UNIQUE (n)-[:CREATED_BY {date: '2015-02-23'}]->(:User {email: '[email protected]'})
RETURN n;

I've removed the starting MATCH clause (because I presume you want to create a CREATED_BY relationship even when that User does not yet exist in the DB), and simplified the ON CREATE to remove the redundant setting of the name property.

I have also added an OPTIONAL MATCH that will only match an n node that does not already have an outgoing CREATED_BY relationship, followed by a CREATE UNIQUE clause that fully specifies the User node.

Cypher, create unique relationship for one node

1 Answers