Chapter 09 · Constraints and Indexes: Range, Text, Point, Token, Full-Text, and Schema Enforcement
Inspect SHOW INDEXES/CONSTRAINTS, Identify Redundant Structures, and Build an Evidence-Based Index Set
Turn AtlasMart schema into an auditable constraint/index set justified by correctness and workload evidence.
Learning outcomes
Schema review is not “list indexes and count them.” AtlasMart must connect each integrity object to a model invariant and each index to an observed query. The final Chapter 09 lab builds a machine-readable schema inventory, compares it with representative plans and usage counters, removes a deliberately unused index, and leaves a documented minimal set.
Build a complete inventory with SHOW CONSTRAINTS and SHOW INDEXES.
Distinguish independently managed indexes from constraint-owned backing indexes.
Use plans, usage counters and workload evidence to identify candidates for removal.
Remove a deliberately unused structure without touching default/token/constraint-backed indexes.
Document the remaining constraint/index set as an operational schema contract.
The mandatory lab continues Neo4j Community
2026.07.1, database neo4j, explicit
CYPHER 25 for version-sensitive examples,
authentication enabled, no mandatory APOC/GDS plugin, and the
AtlasMart identifiers/model established in Chapters 01–08.
Neo4j 5.26.30 remains the LTS comparison line.
Community currently supports node and relationship
property-uniqueness constraints. Property-existence,
property-type and key constraints—and Cypher 25 graph
types—are Enterprise-only; Enterprise examples in this chapter
are optional and are never presented as Community output.
This generation environment does not run Neo4j or Docker.
Commands were checked against current official documentation
but were not executed here. Expected plans, counts, index
states and scores are therefore described by invariant rather
than fabricated as captured output. All disposable data uses
labTag='ch09'; all disposable schema objects are
named ch09_*. Never drop an index/constraint
merely because its name looks similar to a lab object—verify
SHOW INDEXES/SHOW CONSTRAINTS first.
1. Start from invariants and queries, not object count
| Schema object | Reason to keep | Evidence required |
|---|---|---|
| product_id uniqueness | Prevent duplicate stable business IDs under concurrent writes | Constraint inventory + violation test |
| ch09_product_price_range | Price-range API | Representative EXPLAIN/PROFILE + workload presence |
| ch09_product_name_text | Substring/suffix product search | Text predicate plan evidence |
| ch09_product_location_point | Nearby-product/location query | Spatial predicate plan evidence |
| ch09_content_fulltext | Lexical relevance search | Judged retrieval + freshness/score contract |
| Token lookup indexes | Label/type starts | Default inventory + representative label/type plans |
2. Create one deliberately unused index, then audit all objects
CYPHER 25CREATE RANGE INDEX ch09_product_status_unused IF NOT EXISTSFOR (p:Product) ON (p.status);CALL db.awaitIndex('ch09_product_status_unused',300);CYPHER 25SHOW CONSTRAINTSYIELD name,type,entityType,labelsOrTypes,properties,ownedIndexRETURN 'constraint' AS kind,name,type,entityType,labelsOrTypes,properties,ownedIndexORDER BY name;SHOW INDEXESYIELD name,type,state,populationPercent,entityType,labelsOrTypes,properties,owningConstraint,lastRead,readCount,trackedSinceRETURN name,type,state,populationPercent,entityType,labelsOrTypes,properties,owningConstraint,lastRead,readCount,trackedSinceORDER BY type,name;3. Usage counters are evidence, not a verdict
lastRead, readCount and
trackedSince can help identify candidates that have
not been read during the observation period. A zero count is not
sufficient reason to drop an index if the period excludes
month-end jobs, incident workflows, seasonal traffic or a newly
deployed query. Combine counters with query inventory,
logs/traces and plan tests.
CYPHER 25SHOW INDEXESYIELD name,type,state,owningConstraint,lastRead,readCount,trackedSinceWHERE name STARTS WITH 'ch09_'RETURN name,type,state,owningConstraint,lastRead,readCount,trackedSinceORDER BY readCount ASC,name;4. Deliberately wrong: drop the “duplicate-looking” backing index
A uniqueness/key constraint owns its backing range index.
Treating that index as an independent redundant structure
misunderstands the model: the index exists so Neo4j can enforce
uniqueness efficiently. Audit
owningConstraint before any drop decision.
Likewise, default token lookup indexes are not clutter merely
because you did not explicitly create them.
5. Evidence-based removal and rollback thinking
The Chapter 09 representative workload never queries
Product.status, and the fixture does not even
assign that property. That makes
ch09_product_status_unused a deliberate removal
candidate. In production, capture the create statement and
rollback path before dropping any schema object.
CYPHER 25DROP INDEX ch09_product_status_unused IF EXISTS;SHOW INDEXES YIELD name,type,state,owningConstraintWHERE name STARTS WITH 'ch09_'RETURN name,type,state,owningConstraint ORDER BY type,name;6. Chapter 09 acceptance gate
CYPHER 25SHOW CONSTRAINTS YIELD name,type,labelsOrTypes,propertiesRETURN name,type,labelsOrTypes,properties ORDER BY name;SHOW INDEXES YIELD name,type,state,populationPercent,labelsOrTypes,properties,owningConstraintWHERE name STARTS WITH 'ch09_' OR owningConstraint IS NOT NULLRETURN name,type,state,populationPercent,labelsOrTypes,properties,owningConstraintORDER BY type,name;| Acceptance question | Pass condition |
|---|---|
| Integrity objects understood? | Every constraint maps to a named invariant and correct edition boundary |
| Indexes usable? | Required lab indexes are ONLINE before plan/retrieval acceptance |
| Planner evidence captured? | Representative range/text/point/relationship queries have inspected plans |
| Full-text contract explicit? | Analyzer, score semantics and freshness mode are documented |
| Redundant candidate handled? | Only the deliberate unused index was dropped; backing/default indexes remain untouched |
Check your understanding
- Why can readCount=0 be misleading?
- What field distinguishes a backing index?
- Should you benchmark a POPULATING index?
- Why keep create/drop statements with schema decisions?
- What is the final rule for adding an index?
Review the answers
1. The observation window may not include the workload that needs the index.
2. owningConstraint.
3. No. It cannot serve queries until ONLINE.
4. They provide auditable migration and rollback/re-creation paths.
5. A documented query/predicate and measurable evidence should justify its lifecycle/write/storage cost.
7. Cleanup or preserve for Chapter 10
If you are continuing directly to Chapter 10, keep the useful
ch09_* indexes long enough to compare query plans
and selectivity in the tuning lessons. If you want an isolated
reset, use the cleanup below after reviewing every object name.
CYPHER 25MATCH (n) WHERE n.labTag='ch09' DETACH DELETE n;DROP INDEX ch09_product_name_text IF EXISTS;DROP INDEX ch09_product_price_range IF EXISTS;DROP INDEX ch09_product_location_point IF EXISTS;DROP INDEX ch09_product_category_price IF EXISTS;DROP INDEX ch09_contains_unitprice_range IF EXISTS;DROP INDEX ch09_product_status_unused IF EXISTS;DROP INDEX ch09_content_fulltext IF EXISTS;DROP INDEX ch09_review_async_fulltext IF EXISTS;DROP CONSTRAINT ch09_contains_line_id_unique IF EXISTS;Summary and next step
Chapter 09 separates integrity contracts from access paths, verifies state before measurement, and treats full-text as an explicit retrieval subsystem. Chapter 10 builds on this schema evidence to reason about cardinality estimates, physical operators, database hits, memory and plan stability.
Authoritative references
- Current Neo4j versions — Release/LTS snapshot used for this chapter.
- Constraints — Current constraint types and edition boundaries.
- Create constraints — Current uniqueness, existence, type and key syntax and backing-index behavior.
- Search-performance indexes — Range, text, point and token lookup index semantics.
- Show indexes — Index lifecycle, state, population and usage evidence.
- Full-text indexes — Full-text schema, analyzers, query procedures and eventual-consistency behavior.
- Built-in index procedures — db.awaitIndex(es) and full-text refresh/analyzer procedures.
- SHOW constraints — Constraint inventory and owned-index metadata.
- SHOW indexes — State, population and usage-counter evidence.
- Understanding query plans — Plan evidence that bridges directly into Chapter 10.