ALTER TABLE

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

Synopsis

The ALTER TABLE statement changes 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

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 datatype.

Semantics

• An error is raised if table_name does not exists in the associated keyspace.
• Columns that are part of PRIMARY KEY cannot be 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

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

cqlsh:example> ALTER TABLE employees ADD title TEXT;

cqlsh: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

cqlsh:example> ALTER TABLE employees DROP salary;

cqlsh: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

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

cqlsh: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

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

cqlsh: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

CREATE TABLEDELETEDROP TABLEINSERTSELECTUPDATEOther CQL Statements