Manipulates the column metadata of a column family.
ALTER TABLE [<keyspace_name>].<column_family> (ALTER <column_name> TYPE <data_type> | ADD <column_name> <data_type> | DROP <column_name> | WITH <optionname> = <val> [AND <optionname> = <val> [...]]);
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.
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.
You can qualify column family 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 Typed Column
To change the storage type for a column of type ascii to type text, use ALTER TABLE and the ALTER and TYPE keywords in the following way:
ALTER TABLE addamsFamily ALTER lastKnownLocation TYPE text;
The column must already have a type in the column family metadata and the old type must be compatible with the new type. 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 3 Column Family Storage Parameters for the column family options you can define.
Changing any compaction or compression setting erases all previous compaction_strategy_options or compression_parameters settings, respectively.
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; ALTER TABLE addamsFamily WITH compression_parameters:sstable_compression = 'DeflateCompressor' AND compression_parameters:chunk_length_kb = 64; ALTER TABLE users WITH compaction_strategy_class='SizeTieredCompactionStrategy' AND min_compaction_threshold = 6;
|CQL Commands||CQL Shell Commands|