ALTER TABLE
Modifies the columns and properties of a table.
Add new columns, drop existing columns, renames columns, and change table properties. The command returns no results.
- Can only rename clustering columns in the primary key.
- Cannot change the data type of a column.
- For a table that has a materialized view, cannot drop a column from the table even if the column is not used in the materialized view.
- Cannot rename or drop columns that have dependent secondary indexes or Datastax Enterprise Search indexes.
- Do not add a column with the same name as an existing column but with a different data type. It will prevent commit log replays and corrupt existing SSTables with old data.
ALTER COLUMNFAMILY
is deprecated.Synopsis
ALTER TABLE [keyspace_name.]table_name [ ADD ( column_definition | column_definition_list ) ] [ DROP ( column | column_list | COMPACT STORAGE ) ] [ RENAME column_name TO column_name ] [ WITH table_properties [ , ... ] ] ;
Syntax conventions | Description |
---|---|
UPPERCASE | Literal keyword. |
Lowercase | Not literal. |
Italics |
Variable value. Replace with a user-defined value. |
[] |
Optional. Square brackets ( [] ) surround
optional command arguments. Do not type the square brackets. |
( ) |
Group. Parentheses ( ( ) ) identify a group to
choose from. Do not type the parentheses. |
| |
Or. A vertical bar ( | ) separates alternative
elements. Type any one of the elements. Do not type the vertical
bar. |
... |
Repeatable. An ellipsis ( ... ) indicates that
you can repeat the syntax element as often as required. |
'Literal string' |
Single quotation ( ' ) marks must surround
literal strings in CQL statements. Use single quotation marks to
preserve upper case. |
{ key : value
} |
Map collection. Braces ( { } ) enclose map
collections or key value pairs. A colon separates the key and the
value. |
<datatype1,datatype2> |
Set, list, map, or tuple. Angle brackets ( <
> ) enclose data types in a set, list, map, or tuple.
Separate the data types with a comma. |
cql_statement; |
End CQL statement. A semicolon ( ; ) terminates
all CQL statements. |
[--] |
Separate the command line options from the command arguments with
two hyphens ( -- ). This syntax is useful when
arguments might be mistaken for command line options. |
' <schema> ... </schema>
' |
Search CQL only: Single quotation marks ( ' )
surround an entire XML schema declaration. |
@xml_entity='xml_entity_type' |
Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files. |
- ADD column_defintion | ( column_definition_list )
- Add one or more columns and set the data type. Set the column name followed by the
data type. The value is automatically set to null. To add multiple columns, use a comma
separated list of columns placed inside
parentheses.
column_name cql_type [ , ] [ column_name cql_type [ , ... ]
Restriction: Adding columns to a primary key is not supported after a table has been created. - DROP column | ( column_list )
-
Drop one or more columns. The values contained in the row are also dropped and not recoverable. To drop multiple columns, use a comma separated list of columns placed inside parentheses.
- RENAME column_name TO column_name
- Changes the name of a primary key column and preserves the existing values.Restriction: Not supported on materialized view base-tables, or tables with secondary indexes or Datastax Enterprise Search indexes.
- table_properties
-
You can modify an existing table's properties. Some properties are single options that are set to a value:
For example,option_name = value [ AND ... ]
speculative_retry = '10ms'
. Enclose the value for a string property in single quotation marks.Other table properties are set using a JSON map:
option_name = { subproperty_name : value [ , ... ] }
See table_options for more details.
Examples
Specifying the table and keyspace
You can qualify the table name by prepending the name of its keyspace. For example, to
specify the teams
table in the cycling
keyspace:
ALTER TABLE cycling.teams ADD manager uuid;
Adding a column
To add a column to a table, use the ADD instruction; for example:
ALTER TABLE cycling.cyclist_races ADD firstname text;
To add a column of a collection type:
ALTER TABLE cycling.upcoming_calendar ADD events list<text>;
This operation does not validate the existing data.
You cannot use the ADD instruction to add:
- A column with the same name as an existing column.
- A static column if the table has no clustering columns and uses
COMPACT STORAGE
.
Dropping a column
ALTER TABLE cycling.basic_info DROP birth_year;
DROP removes the column from the table definition. The column becomes unavailable for queries immediately after it is dropped. The database drops the column data during the next compaction.
- If you drop a column then re-add it, DataStax Distribution of Apache Cassandra™ does not restore the values written before the column was dropped.
- Do not re-add a dropped column that contained timestamps generated by a client; you can re-add columns with timestamps generated by the write time facility.
- You cannot drop columns from tables defined with the COMPACT STORAGE option.
Renaming a column
- You can only rename clustering columns, which are part of the primary key.
- You cannot rename the partition key because the partition key determines the data
storage location on a node. If a different partition name is required, the table must
be recreated and the data migrated.Note: There are many restrictions when using RENAME because SSTables are immutable. To change the state of things on disk, everything must be rewritten.
- You can index a renamed column.
- You cannot rename a column if an index has been created on it.
- You cannot rename a static column.
Modifying table properties
- Single property name and value.
- Property map to set the names and values, as shown in the next section on compression and compaction.
This example uses the WITH instruction to modify the read_repair_chance property, which configures read repair for tables that use for a non-quorum consistency and how to change multiple properties using AND:
ALTER TABLE cyclist_mv WITH comment = 'ID, name, birthdate and country' AND read_repair_chance = 0.2;
Enclose a text property value in single quotation marks. You cannot modify properties of a table that uses COMPACT STORAGE.
Modifying compression and compaction
Use a property map to alter a table's compression or compaction setting:
ALTER TABLE cycling_comments WITH compression = { 'sstable_compression' : 'DeflateCompressor', 'chunk_length_kb' : 64 };
Enclose the name of each key in single quotes. If the value is a string, enclose this in quotes as well.
Changing caching
Set the number of rows per partition to store in the row cache for a table to 10 rows:
ALTER TABLE cycling.events WITH caching = { 'keys': 'NONE', 'rows_per_partition': 10 };
Reviewing the table definition
DESC TABLE cycling.events;
The table details including the column names are returned.
CREATE TABLE cycling.events ( month int, end timestamp, class text, title text, location text, start timestamp, type text, PRIMARY KEY (month, end, class, title) ) WITH CLUSTERING ORDER BY (end ASC, class ASC, title ASC) AND bloom_filter_fp_chance = 0.01 AND caching = {'keys': 'NONE', 'rows_per_partition': '10'} AND comment = '' AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'} AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND crc_check_chance = 1.0 AND dclocal_read_repair_chance = 0.1 AND default_time_to_live = 0 AND gc_grace_seconds = 864000 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 0 AND min_index_interval = 128 AND read_repair_chance = 0.0 AND speculative_retry = '99PERCENTILE';