apache/cassandra · error · InvalidRequestException

Cannot create materialized view '%s' without primary key col

Error message

Cannot create materialized view '%s' without primary key columns %s from base table '%s'

What it means

At least one of the view's key columns (partition or clustering) must be a clustering column of the base table; the view must narrow rows differently than the base partitioning. A view whose key adds nothing new (only reusing base partition keys) would be a duplicate partitioning and is rejected.

Source

Thrown at src/java/org/apache/cassandra/cql3/statements/schema/CreateViewStatement.java:284

        });

        // If we give a clustering order, we must explicitly do so for all aliases and in the order of the PK
        if (!clusteringOrder.isEmpty() && !clusteringColumns.equals(new ArrayList<>(clusteringOrder.keySet())))
            throw ire("Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive");

        /*
         * We need to include all of the primary key columns from the base table in order to make sure that we do not
         * overwrite values in the view. We cannot support "collapsing" the base table into a smaller number of rows in
         * the view because if we need to generate a tombstone, we have no way of knowing which value is currently being
         * used in the view and whether or not to generate a tombstone. In order to not surprise our users, we require
         * that they include all of the columns. We provide them with a list of all of the columns left to include.
         */
        List<ColumnIdentifier> missingPrimaryKeyColumns =
            Lists.newArrayList(filter(transform(table.primaryKeyColumns(), c -> c.name), c -> !primaryKeyColumns.contains(c)));

        if (!missingPrimaryKeyColumns.isEmpty())
        {
            throw ire("Cannot create materialized view '%s' without primary key columns %s from base table '%s'",
                      viewName, join(", ", transform(missingPrimaryKeyColumns, ColumnIdentifier::toString)), tableName);
        }

        Set<ColumnIdentifier> regularBaseTableColumnsInViewPrimaryKey = new HashSet<>(primaryKeyColumns);
        transform(table.primaryKeyColumns(), c -> c.name).forEach(regularBaseTableColumnsInViewPrimaryKey::remove);
        if (regularBaseTableColumnsInViewPrimaryKey.size() > 1)
        {
            throw ire("Cannot include more than one non-primary key column in materialized view primary key (got %s)",
                      join(", ", transform(regularBaseTableColumnsInViewPrimaryKey, ColumnIdentifier::toString)));
        }

        /*
         * Process WHERE clause
         */
        if (whereClause.containsTokenRelations())
            throw new InvalidRequestException("Cannot use token relation when defining a materialized view");

        if (whereClause.containsCustomExpressions())

View on GitHub (pinned to 88fd0f6a0e)

Solutions

Include at least one base clustering column in the view's PRIMARY KEY (typically as partition key of the view)
Remove the view if the base table's key already provides the needed access pattern
Use a separate denormalized table populated by application logic for alternative partitionings the view cannot express

Example fix

// before
CREATE MATERIALIZED VIEW mv AS SELECT pk, ck FROM base WHERE pk IS NOT NULL AND ck IS NOT NULL PRIMARY KEY (pk);
// after
CREATE MATERIALIZED VIEW mv AS SELECT pk, ck FROM base WHERE pk IS NOT NULL AND ck IS NOT NULL PRIMARY KEY (ck, pk);

Defensive patterns

Strategy: validation

Validate before calling

boolean hasBaseClustering = viewKeyCols.stream().anyMatch(baseClusteringKeys::contains); if (!hasBaseClustering) throw new IllegalArgumentException("MV key must include a base clustering column");

Try / catch

try { session.execute(createMvStmt); } catch (InvalidQueryException e) { if (e.getMessage().contains("can only be done on tables with clustering columns")) { /* rework key to include a base clustering column */ } }

Prevention

Design MVs to index rows by base clustering columns; they are not for re-partitioning by base partition keys alone
Use denormalized base tables for alternative partitionings

When it happens

Trigger: CREATE MATERIALIZED VIEW on a base table where the view PRIMARY KEY columns are all base partition key columns with no base clustering column among them, on a table that has clustering columns.

Common situations: Trying to make a view that just re-partitions by base partition keys; misunderstanding the requirement that MVs index by base clustering columns; crafting a key purely from base partition key columns on a table with clustering columns.

Understand the failure class

Background: Schema validation failed / invalid input schema: payload rejected because its shape doesn't match the expected schema — this error's family across 28 libraries.

Related errors

AI-assisted analysis of apache/cassandra@88fd0f6a0e (2026-09-10). Data as JSON: /api/errors/7a2ffd2ab8be8fdc. Report an issue: GitHub.