ALTER TABLE¶

Synopsis¶

ALTER TABLE <name>
   (ALTER <column_name> TYPE <data_type>
 | ADD <column_name> <data_type>
 | DROP <column_name>
 | WITH <optionname> = <val> [AND <optionname> = <val> [...]]);

Description¶

ALTER TABLE manipulates the column family metadata. You can change the data storage type of columns, add new columns, drop existing columns, and change column family properties. No results are returned.

You can also use the alias ALTER COLUMNFAMILY.

See CQL Data Types for the available data types and CQL Column Family Storage Parameters for column properties and their default values.

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

Changing the Type of a Typed Column

To change the storage type for a column, use ALTER TABLE and the ALTER and TYPE keywords in the following way:

ALTER TABLE addamsFamily ALTER lastKnownLocation TYPE uuid;

The column must already have a type in the column family metadata. The column may or may not already exist in current rows. No validation of existing data occurs. The bytes stored in values for that column remain unchanged, and if existing data is not deserializable according to the new type, your CQL driver or interface might report errors.

Adding a Typed Column

To add a typed column to a column family, use ALTER TABLE and the ADD keyword in the following way:

ALTER TABLE addamsFamily ADD gravesite varchar;

The column must not already have a type in the column family metadata. The column may or may not already exist in current rows. No validation of existing data occurs.

Dropping a Typed Column

To drop a typed column from the column family metadata, use ALTER TABLE and the DROP keyword in the following way:

ALTER TABLE addamsFamily DROP gender;

Dropping a typed column does not remove the column from current rows; it just removes the metadata saying that the bytes stored under that column are expected to be deserializable according to a certain type.

Modifying Column Family Options

To change the column family storage options established during creation of the column family, 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 column family'
  AND read_repair_chance = 0.2;

See CQL Column Family Storage Parameters for the column family options you can define.

Setting compaction_strategy_options or compression_options erases all previous compaction_strategy_options or compression_options settings, respectively.

Examples¶

ALTER TABLE users ALTER email TYPE varchar;

ALTER TABLE users ADD gender varchar;

ALTER TABLE users DROP gender;

ALTER TABLE users WITH comment = 'active users' AND read_repair_chance = 0.2;