Cassandra Alter Table

Cassandra - Select Table

Cassandra - Truncate Table

The Alter Table command is used to change the structure of the Cassandra table such as adding new columns, dropping existing columns, renaming a column, making changes in the property of the table, and so on. The only restriction is that we can't alter the Primary Key of the table and we can't alter the columns which are part of the Materialized view.

Alter Table

We can use the Cassandra Alter Table to perform the following operations. The syntax of Alter command is as below.

1. Adding a Column
2. Rename a Column
3. Dropping a Column

Syntax:

ALTER TABLE [keyspacename.] tablename
[ALTER columnname TYPE cql_type]
[ADD (column_definition_list)]
[DROP column_list | COMPACT STORAGE ]
[RENAME columnname TO columnname]
[WITH table_properties];

Let's see each Alter command option in the below section. We have a table name employee that we created in the Create table section. The table employee is present in cloudduggu keyspace. We will use this table to perform the addition, deletion, and renaming of the columns.

1. Adding a Column

We can use Cassandra Alter Command to add a column in the table. While adding a column we need to make sure that the same name column should not be present in the table and the table is not created with compact storage option.

Syntax:

ALTER TABLE table_name
ADD  new_column DATATYPE;

We will add a new column name empgender in the employee table. The command is as below.

Command:

cqlsh:cloudduggu> ALTER TABLE cloudduggu.employee ADD empgender TEXT;
cqlsh:cloudduggu> describe cloudduggu.employee;

Output:

2. Rename a Column

The Cassandra Alter Command is used to rename the existing column name as well to the new name.

Syntax:

ALTER TABLE table_name
rename column_name TO column_name;

In the below example, we will use the Cassandra Alter Command to rename the existing column empid to a new name employee_id.

Please note, We can RENAME only those clustering columns in Cassandra which are part of the Primary Key. If we try to rename columns that are not part of Primary Key then we will get the error InvalidRequest: Error from server: code=2200 [Invalid query] message=Cannot rename non PRIMARY KEY column.

Command:

cqlsh:cloudduggu> ALTER TABLE cloudduggu.employee rename empid TO employee_id;

Output:

3. Dropping a Column

In Cassandra, we can use the Alter Command to delete the column from the table structure. Before dropping a column we need to make sure that the table should not be defined with the option compact storage.

Syntax:

ALTER TABLE keyspace_name. table_name
DROP column_list;

In the following example, we will use the Alter Table command of Cassandra to drop the empstate column from the table cloudduggu.employee.

Command:

cqlsh:cloudduggu> ALTER TABLE cloudduggu.employee DROP empstate;

Output: