ALTER TABLE

Modifies columns and table properties. Returns no results.

Limitations and warnings

Category Limitations Warnings Recommended approach

Primary key

Cannot add columns to the primary key.

To change the primary key definition, create a new table.

Column names

Cannot rename columns except clustering columns in the primary key.

Cannot rename any columns in tables with indexes.

Cannot rename STATIC columns.

Renaming columns is severely restricted because SSTables are immutable. Changes to column names would require rewriting all existing SSTables.

Don’t add a column with the same name as a previous column with a different data type. This condition prevents commit log replays and corrupts existing SSTables.

Recycled column names with the same data type are acceptable. However, the database doesn’t restore values written before dropping the column.

For non-primary key columns, drop and re-create the column with the desired name. You must drop the column first because a table cannot have multiple columns with the same name.

If blocked by the presence of another database object, drop the database object, drop and re-create the column, and then re-create the database object.

To change the primary key definition, create a new table.

Data types

Cannot change a column’s data type.

Don’t add a column with the same name as a previous column with a different data type. This condition prevents commit log replays and corrupts existing SSTables.

Drop and re-create the column with a unique name and the desired data type.

Don’t reuse the previous column name.

Indexes

Cannot rename or drop indexed columns.

Drop the index, rename or drop the column, and then re-create the index.

Case insensitivity

CQL enforces lowercase by default.

Prefer lowercase names when possible. Use double quotes if you need to preserve casing in the names of keyspaces, tables, columns, and specialized data types.

Client-generated timestamps

Don’t drop-and-re-create a column that contains client-generated timestamps.

Don’t recycle names of columns that contained client-generated timestamps, even if the new column also stores client-generated timestamps.

These warnings don’t apply to columns with timestamps generated by the write time facility. It is safe to drop-and-re-create these columns if the new column has the same data type.

Synopsis

ALTER TABLE [<keyspace_name>.]<table_name>
  [ ADD ( <column_definition> | <column_definition_list> ) [ , ... ] ]
  [ DROP <column_name> [ , ... ] ]
  [ RENAME <column_name> TO <column_name> ];

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	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.

Options

See Limitations and warnings for information about restricted and problematic ALTER TABLE operations.

ADD ( <column_definition> | <column_definition_list> )

Add one or more columns and set the column data types.

Specify the column names followed by the data types. To add multiple columns, use a comma separated list of columns placed inside parentheses.

<column_name> <cql_type> [ , ]
[ <column_name> <cql_type> [ , ... ] ]

For all existing rows, a new column’s value is automatically set to null.

Column names must be unique within the table.

You cannot add STATIC columns to a table with no clustering columns.

DROP ( <column> | <column_list> )

Drop one or more columns and the data in those columns. To drop multiple columns, use a comma separated list of columns enclosed in parentheses.

RENAME <column_name> TO <column_name>

Changes the name of a clustering column in the primary key. Preserves the existing values.

WITH table_properties

Modify table properties using the same syntax as the CREATE TABLE command:

Use the appropriate mapping style (plain key-value pair or simple JSON object) for each property.
Enclose the name of each key in single quotes.
If the value is a string, enclose the string in quotes as well.
Use AND to chain multiple table property modifications in a single ALTER TABLE statement.

Some modifications can impact performance temporarily or permanently. For example, changing the compaction strategy requires rewriting all existing SSTAbles.

For more information about available table properties, including subproperties and performance impacts, see CREATE TABLE.

Examples

The following examples illustrate various ALTER TABLE operations.

After you run ALTER TABLE, verify the changes with the CQL shell DESCRIBE TABLE command.

Some modifications can cause errors, such as SSTable corruption. ALTER TABLE doesn’t block all dangerous modifications. See Limitations and warnings for information about restricted and problematic ALTER TABLE operations.

Add a column

Use the ADD instruction to add one or more columns to a table. The ALTER TABLE command creates the column metadata, adds the column to the table schema, and sets the value to null for all existing rows. This operation does not validate the existing data.

Add one non-collection column

ALTER TABLE cycling.cyclist_races
  ADD manager UUID;

ALTER TABLE cycling.cyclist_alt_stats
  ADD cyclist_age int;

Add one collection column

ALTER TABLE cycling.cyclist_races
  ADD completed list<text>;

ALTER TABLE cycling.upcoming_calendar
  ADD description map<text,text>;

Drop a column

The DROP instruction removes the specified column from the table definition. The column becomes unavailable for queries immediately after it is dropped. Data stored in the column is marked with tombstones and dropped during the next compaction.

The column and all of it’s data are deleted. If you need to preserve the data, take action to extract the data before you drop the column.

ALTER TABLE cycling.cyclist_alt_stats
  DROP cyclist_age;

ALTER TABLE cycling.cyclist_races
  DROP manager;

Rename a primary key clustering column

Only primary key clustering columns can be renamed. All other columns cannot be renamed. See Limitations and warnings for more information.

ALTER TABLE cycling.race_times
  RENAME race_date TO date;

ALTER TABLE cycling.cyclist_alt_stats
  RENAME id TO cyclist_id;

ALTER TABLE

Limitations and warnings

Synopsis

Options

Examples

Add a column

Drop a column

Rename a primary key clustering column

See also

Was this helpful?

Give Feedback