ALTER TABLE

Modify the column metadata of a table.

Modify the column metadata of a table.

Synopsis 

ALTER TABLE keyspace_name.table_name instruction
instruction is:
ALTER column_name TYPE cql_type
| ( ADD column_name cql_type )
| ( DROP column_name )
| ( RENAME column_name TO column_name )
| ( WITH property AND property ... )

cql_type is compatible with the original type and is a CQL type, other than a collection or counter. Exceptions: ADD supports a collection type and also, if the table is a counter, a counter type.

property is a CQL table property and value, such as speculative_retry = '10ms'. Enclose a string property in single quotation marks.

Legend
  • Uppercase means literal
  • Lowercase means not literal
  • Italics mean optional
  • The pipe (|) symbol means OR or AND/OR
  • Ellipsis (...) means repeatable

A semicolon that terminates CQL statements is not included in the synopsis.

Description 

ALTER TABLE manipulates the table metadata. You can change the data storage type of columns, add new columns, drop existing columns, and change table properties. No results are returned. You can also use the alias ALTER COLUMNFAMILY.

First, specify the name of the table to be changed after the ALTER TABLE keywords, followed by the type of change: ALTER, ADD, DROP, RENAME, or WITH. Next, provide the rest of the needed information, as explained in the following sections.

You can qualify table names by keyspace. For example, to alter the addamsFamily table in the monsters keyspace:

ALTER TABLE monsters.addamsFamily ALTER lastKnownLocation TYPE uuid;

Changing the type of a column 

To change the storage type for a column, the type you are changing to and from must be compatible. For example, change the type of the bio column in the users table from ascii to text, and then from text to blob.

CREATE TABLE users (
  user_name varchar PRIMARY KEY, 
  bio ascii,
  );
ALTER TABLE users ALTER bio TYPE text;
ALTER TABLE users ALTER password TYPE blob;

Altering the type of a column after inserting data can confuse CQL drivers/tools if the new type is incompatible with the data. The bytes stored in values for that column remain unchanged, and if existing data cannot be deserialized according to the new type, your CQL driver or interface might report errors.

These changes to a column type are not allowed:

The column, in this example bio, must already exist in current rows.

Adding a column 

To add a column, other than a column of a collection type, to a table, use ALTER TABLE and the ADD keyword in the following way:

ALTER TABLE addamsFamily ADD gravesite varchar;

To add a column of the collection type:

ALTER TABLE users ADD top_places list<text>;

The column may or may not already exist in current rows. No validation of existing data occurs.

These additions to a table are not allowed:

  • Adding a column having the same name as an existing column.
  • Adding columns to tables defined with COMPACT STORAGE.

Dropping a column 

This feature is not ready in Cassandra 1.2 but will be available in a subsequent version. To drop a column from the table, use ALTER TABLE and the DROP keyword. Dropping a column removes the column from current rows.
ALTER TABLE addamsFamily DROP gender;

Renaming a column 

The main purpose of the RENAME clause is to change the names of CQL 3-generated primary key and column names that are missing from a legacy table.

Modifying table properties 

To change the table properties established during creation of the table, use ALTER TABLE and the WITH keyword. To change multiple properties, use AND as shown in this example:

ALTER TABLE addamsFamily
  WITH comment = 'A most excellent and useful table'
  AND read_repair_chance = 0.2;

The CQL 3 table properties list the table options you can define. Enclose a property in single quotation marks. You cannot modify table options of a table having compact storage.

Modifying the compression or compaction setting 

Changing any compaction or compression option erases all previous compaction or compression settings.
ALTER TABLE addamsFamily
  WITH compression =
  { 'sstable_compression' : 'DeflateCompressor', 'chunk_length_kb' : 64 };

ALTER TABLE users
  WITH compaction =
  { 'class' : 'SizeTieredCompactionStrategy', 'min_threshold' : 6 };