{"record":{"id":"e07b378a32671e3c","repo":"apache/cassandra","slug":"cannot-create-more-than-one-storage-attached-index","errorCode":null,"errorMessage":"Cannot create more than one storage-attached index on the same column: ","messagePattern":"Cannot create more than one storage-attached index on the same column: ","errorType":"validation","errorClass":"InvalidRequestException","httpStatus":null,"severity":"error","filePath":"src/java/org/apache/cassandra/index/sai/StorageAttachedIndex.java","lineNumber":274,"sourceCode":"        }\n\n        Pair<ColumnMetadata, IndexTarget.Type> target = TargetParser.parse(metadata, targetColumn);\n\n        if (target == null)\n        {\n            throw new InvalidRequestException(\"Failed to retrieve target column for: \" + targetColumn);\n        }\n\n        // In order to support different index targets on non-frozen map, ie. KEYS, VALUE, ENTRIES, we need to put index\n        // name as part of index file name instead of column name. We only need to check that the target is different\n        // between indexes. This will only allow indexes in the same column with a different IndexTarget.Type.\n        //\n        // Note that: \"metadata.indexes\" already includes current index\n        if (metadata.indexes.stream().filter(index -> index.getIndexClassName().equals(StorageAttachedIndex.class.getName()))\n                            .map(index -> TargetParser.parse(metadata, index.options.get(IndexTarget.TARGET_OPTION_NAME)))\n                            .filter(Objects::nonNull).filter(t -> t.equals(target)).count() > 1)\n        {\n            throw new InvalidRequestException(\"Cannot create more than one storage-attached index on the same column: \" + target.left);\n        }\n\n        Map<String, String> analysisOptions = AbstractAnalyzer.getAnalyzerOptions(options);\n        if (target.left.isPrimaryKeyColumn() && !analysisOptions.isEmpty())\n        {\n            throw new InvalidRequestException(ANALYSIS_ON_KEY_COLUMNS_MESSAGE + new CqlBuilder().append(analysisOptions));\n        }\n\n        IndexTermType indexTermType = IndexTermType.create(target.left, metadata.partitionKeyColumns(), target.right);\n        AbstractAnalyzer.fromOptions(indexTermType, analysisOptions);\n        IndexWriterConfig config = IndexWriterConfig.fromOptions(null, indexTermType, options);\n\n        // If we are indexing map entries we need to validate the subtypes\n        if (indexTermType.isComposite())\n        {\n            for (IndexTermType subType : indexTermType.subTypes())\n            {\n                if (!SUPPORTED_TYPES.contains(subType.asCQL3Type()) && !subType.isFrozen())","sourceCodeStart":256,"sourceCodeEnd":292,"githubUrl":"https://github.com/apache/cassandra/blob/88fd0f6a0eaed8943f05ac9e8f947882b8ddc8f1/src/java/org/apache/cassandra/index/sai/StorageAttachedIndex.java#L256-L292","documentation":"SAI (Storage-Attached Index) rejects CREATE INDEX when another SAI index already exists on the same target column. During index option validation the metadata's existing indexes are filtered to SAI indexes, their targets are parsed, and if more than one matches the requested target the request is invalid. This prevents duplicate indexes that would waste space and confuse query planning.","triggerScenarios":"Calling CREATE CUSTOM INDEX ... USING 'StorageAttachedIndex' on a column that already has an SAI index (the new index is already present in metadata.indexes, so the count of matching parsed targets exceeds 1).","commonSituations":"Re-running a schema migration/idempotent CREATE INDEX script without IF NOT EXISTS; creating a second index on the same column hoping for different analyzer options; copying index DDL across keyspaces.","solutions":["Use CREATE CUSTOM INDEX IF NOT EXISTS to skip creation when the index already exists.","Query system_schema.indexes to check for an existing SAI index on the column before creating one.","If different options are needed, DROP the existing index first, then create the new one.","Index a different column or use a different index implementation if you genuinely need a second index."],"exampleFix":"// before\nCREATE CUSTOM INDEX ON ks.tbl (col) USING 'StorageAttachedIndex';\n// after\nCREATE CUSTOM INDEX IF NOT EXISTS ON ks.tbl (col) USING 'StorageAttachedIndex';","handlingStrategy":"validation","validationCode":"SELECT column_name FROM system_schema.indexes WHERE keyspace_name='ks' AND table_name='tbl' AND options CONTAINS 'StorageAttachedIndex'; // then check column in app code before CREATE INDEX","typeGuard":null,"tryCatchPattern":"try { session.execute(createIndexCql); } catch (InvalidRequestException e) { if (e.getMessage().startsWith(\"Cannot create more than one storage-attached index\")) log.info(\"index already exists, ignoring\"); else throw e; }","preventionTips":["Always use IF NOT EXISTS for idempotent schema migrations","Keep one SAI index definition per column in migration scripts","Introspect system_schema.indexes before DDL"],"tags":["cassandra","sai","schema","duplicate-index"],"backgroundTag":"schema-validation-failed","analyzedSha":"88fd0f6a0eaed8943f05ac9e8f947882b8ddc8f1","analyzedAt":"2026-09-10T07:29:22.284Z","contentChangedAt":"2026-09-10T07:29:22.284Z","schemaVersion":2},"datasetVersion":"2026-09-14T21:17:11.552Z"}