ALTER TABLE

Synopsis

Use the ALTER TABLE statement to change the schema or definition of an existing table. It allows adding, dropping, or renaming a column as well as updating a table property.

Syntax

Diagram

ALTERTABLEtable_nameADD,column_namecolumn_typeDROP,column_nameRENAME,column_nameTOcolumn_nameWITHANDproperty_name=property_literal

Grammar

alter_table ::= ALTER TABLE table_name alter_operator [ alter_operator ...]

alter_operator ::= add_op | drop_op | rename_op | property_op

add_op ::= ADD column_name column_type [ ',' column_name column_type ...]

drop_op ::= DROP column_name [ ',' column_name ...]

rename_op ::= RENAME column_name TO column_name [ ',' column_name TO column_name ...]

property_op ::= WITH property_name '=' property_literal [ AND property_name '=' property_literal ...]

Where

  • table_name, column_name, and property_name are identifiers (table_name may be qualified with a keyspace name).
  • property_literal is a literal of either boolean, text, or map data type.

Semantics

  • An error is raised if table_name does not exist in the associated keyspace.
  • Columns that are part of PRIMARY KEY cannot be altered.
  • When adding a column, its value for all existing rows in the table defaults to null.
  • After dropping a column, all values currently stored for that column in the table are discarded (if any).

Examples

Add a column to a table

ycqlsh:example> CREATE TABLE employees (id INT, name TEXT, salary FLOAT, PRIMARY KEY((id), name));

ycqlsh:example> ALTER TABLE employees ADD title TEXT;

ycqlsh:example> DESCRIBE TABLE employees;

Following result would be shown.

CREATE TABLE example.employees (
    id int,
    name text,
    salary float,
    title text,
    PRIMARY KEY (id, name)
) WITH CLUSTERING ORDER BY (name ASC);

Remove a column from a table

ycqlsh:example> ALTER TABLE employees DROP salary;

ycqlsh:example> DESCRIBE TABLE employees;

Following result would be shown.

CREATE TABLE example.employees (
    id int,
    name text,
    title text,
    PRIMARY KEY (id, name)
) WITH CLUSTERING ORDER BY (name ASC);

Rename a column in a table

ycqlsh:example> ALTER TABLE employees RENAME title TO job_title;

ycqlsh:example> DESCRIBE TABLE employees;

Following result would be shown.

CREATE TABLE example.employees (
    id int,
    name text,
    job_title text,
    PRIMARY KEY (id, name)
) WITH CLUSTERING ORDER BY (name ASC);

Update a table property

You can do this as shown below.

ycqlsh:example> ALTER TABLE employees WITH default_time_to_live = 5;

ycqlsh:example> DESCRIBE TABLE employees;

Following result would be shown.

CREATE TABLE example.employees (
    id int,
    name text,
    job_title text,
    PRIMARY KEY (id, name)
) WITH CLUSTERING ORDER BY (name ASC)
    AND default_time_to_live = 5;

See also