Convert a property index to a constraint

apoc
cypher
(Mike Blum+Neo4j) #1

Is there a way to drop a single property’s contraints + index?

RETURN CASE WHEN apoc.schema.node.indexExists('ClientCredentials', ['clientId']) THEN DROP INDEX ON :ClientCredentials(clientId) END;

Won't compile since I don't think you can have sub-queries in CASE statements

my issue was an index previously existed and when i tried this it throws an error if no constraint exists
use case is existing index needs to be come a constraint:

drop existing client:Id index to create a constraint

DROP INDEX ON :ClientCredentials(clientId); CREATE CONSTRAINT ON (cc:ClientCredentials) ASSERT cc.clientId IS UNIQUE;

this query works correctly if there is an existing index but fails if there isn’t one

Neo.DatabaseError.Schema.ConstraintDropFailed: Unable to drop Constraint( UNIQUE, :ClientCredentials(clientId) ): No such constraint Constraint( UNIQUE, :ClientCredentials(clientId) ).

using CALL apoc.schema.assert it dropped all other unspecified indexes / constrinats:
oh. Looks like the dropExisting flag is very important here:
CALL apoc.schema.assert(null, {ClientCredentials:['clientId']}, false);

:cold_sweat: always forget the default is true: https://neo4j-contrib.github.io/neo4j-apoc-procedures/index32.html#_schema_2

But that stops the schema.assert call from dropping the old index:

Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedureapoc.schema.assert: Caused by: org.neo4j.kernel.api.exceptions.schema.AlreadyIndexedException: There already exists an index :label[7](property[38]). A constraint cannot be created until the index has been dropped.

tl;dr: given existing indexes in some databases is thre a way to no-op if an index or constraint doesn't exist or already exists?

0 Likes

(Mike Blum+Neo4j) #2

While not ideal this seems to work correctly:

// drop existing client:Id index to create constraint

CALL apoc.schema.assert({ClientCredentials:['clientId']}, {}, false);
DROP INDEX ON :ClientCredentials(clientId);
CALL apoc.schema.assert({}, {ClientCredentials:['clientId']}, false);

0 Likes