DbSchema | Cassandra - How to Alter a Table?

Table of Contents

1. Introduction
2. What ALTER TABLE supports
3. Prerequisites
4. What ALTER TABLE does not support
5. Altering a table in cqlsh
   • Adding Columns
   • Dropping Columns
   • Renaming Primary-Key Columns
   • Modifying Table Properties
6. Altering a table in DbSchema
7. Common mistakes
8. Conclusion
9. References

Introduction

ALTER TABLE in Cassandra is useful, but it is not as flexible as SQL users often expect. Cassandra lets you evolve tables in controlled ways - for example by adding a column or changing table options - but core modeling choices such as the primary key and clustering order are effectively fixed after creation.

This distinction matters because many Cassandra schema changes that look simple in relational systems actually require creating a new table and migrating data.

What ALTER TABLE supports

Current Cassandra DDL supports these ALTER TABLE operations:

• ADD a new non-primary-key column
• DROP an existing column
• RENAME a primary-key column
• WITH to change table options such as comment, caching, compression, compaction, gc_grace_seconds, or default_time_to_live

Prerequisites

To follow along with the examples in this article, you will need:

• a running Cassandra cluster
• access through cqlsh or DbSchema
• a table that already exists, such as one created in How to Create a Table in Cassandra

What ALTER TABLE does not support

Cassandra does not support a number of SQL-style schema edits:

• you cannot change the partition key
• you cannot add or remove clustering columns
• you cannot change CLUSTERING ORDER BY
• you cannot rename a table with ALTER TABLE
• you should not assume arbitrary column type changes are part of the normal Cassandra workflow

If you need one of those changes, the safe pattern is:

1. create a new table with the desired definition
2. backfill or copy the data
3. switch the application to the new table
4. drop the old table when you are sure it is no longer needed

Altering a table in cqlsh

Adding Columns

Adding a new non-key column is a lightweight schema change in Cassandra.

ALTER TABLE ecommerce.orders_by_customer_day
ADD shipping_method text;

Use this when new attributes need to be stored, but the existing primary key design still matches the query pattern.

Dropping Columns

Dropping a column removes it from the schema immediately, but the on-disk data is cleaned up lazily during compaction.

ALTER TABLE ecommerce.orders_by_customer_day
DROP legacy_status;

This means two things:

• the column becomes unavailable right away
• disk space may not shrink immediately because compaction reclaims the old data later

Renaming Primary-Key Columns

Cassandra allows renaming primary-key columns, not arbitrary non-key columns.

ALTER TABLE ecommerce.orders_by_customer_day
RENAME order_time TO created_at;

Because application code, dashboards, and exports often depend on column names, treat renames as an application change, not just a database change.

Modifying Table Properties

Cassandra lets you update table options with the WITH clause:

ALTER TABLE ecommerce.orders_by_customer_day
WITH comment = 'Orders grouped by customer and day'
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'};

You can also change compaction or compression, but there is one important caveat: when you set a compaction or compression map, re-specify all the sub-options you want to keep, because partial updates replace the previous map.

Altering a table in DbSchema

In DbSchema you can:

1. connect to the Cassandra cluster
2. open the table in the design model
3. add or remove non-key columns visually
4. edit table properties and review the generated CQL
5. deploy the change to the live database when you are ready

DbSchema helps because you can compare the model and the live schema before applying changes, which is safer than making repeated ad hoc edits directly in production.

Common mistakes

These are the mistakes to avoid:

• trying to rename a table with ALTER TABLE
• assuming clustering order can be changed after creation
• dropping columns without considering tombstones and delayed cleanup
• changing compaction or compression maps without re-specifying the full option set
• making schema changes while the cluster is unhealthy or unstable

Conclusion

ALTER TABLE is useful in Cassandra, but it is best for small, intentional schema evolution, not for redesigning the data model. Add columns, drop columns carefully, rename primary-key columns when necessary, and use WITH for table options. For bigger structural changes, create a new table and migrate.

References

• Apache Cassandra CQL DDL documentation
• Apache Cassandra documentation
• DbSchema Cassandra page