ALTER TABLE

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

ALTER TABLE - 图1

Grammar

  1. alter_table ::= ALTER TABLE table_name alter_operator [ alter_operator ...]
  3. alter_operator ::= add_op | drop_op | rename_op | property_op
  5. add_op ::= ADD column_name column_type [ ',' column_name column_type ...]
  7. drop_op ::= DROP column_name [ ',' column_name ...]
  9. rename_op ::= RENAME column_name TO column_name [ ',' column_name TO column_name ...]
  11. 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 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

  1. cqlsh:example> CREATE TABLE employees (id INT, name TEXT, salary FLOAT, PRIMARY KEY((id), name));
  1. cqlsh:example> ALTER TABLE employees ADD title TEXT;
  1. cqlsh:example> DESCRIBE TABLE employees;

Following result would be shown.

  1. CREATE TABLE example.employees (
  2.     id int,
  3.     name text,
  4.     salary float,
  5.     title text,
  6.     PRIMARY KEY (id, name)
  7. ) WITH CLUSTERING ORDER BY (name ASC);

Remove a column from a table

  1. cqlsh:example> ALTER TABLE employees DROP salary;
  1. cqlsh:example> DESCRIBE TABLE employees;

Following result would be shown.

  1. CREATE TABLE example.employees (
  2.     id int,
  3.     name text,
  4.     title text,
  5.     PRIMARY KEY (id, name)
  6. ) WITH CLUSTERING ORDER BY (name ASC);

Rename a column in a table

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

Following result would be shown.

  1. CREATE TABLE example.employees (
  2.     id int,
  3.     name text,
  4.     job_title text,
  5.     PRIMARY KEY (id, name)
  6. ) WITH CLUSTERING ORDER BY (name ASC);

Update a table property

You can do this as shown below.

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

Following result would be shown.

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

See also