Cassandra Alter Table To alter an existing table, the ALTER TABLE command is used. This command can be used to add a column or to Drop a column. Syntax:
ALTER (TABLE | COLUMN_FAMILY) tablename instruction
To Add a Column:
The column name should not conflict with the existing column names. The table should not be defined with a compact storage option.
Syntax:
ALTER TABLE table_name ADD new column datatype; |
Example: Employees table before alteration:
id name salary 1 Adi 50000 2 Bruno 30000 3 Chris 60000 4 Davis 20000 5 Eliza 15000
Query:
ALTER TABLE employees
ADD city text; |
Explanation: The ’employees’ is an already created table. A new column named ‘city’ is added to the table. Verify it by using the below command. SELECT * FROM employees; Output:
id name salary city 1 Adi 50000 2 Bruno 30000 3 Chris 60000 4 Davis 20000 5 Eliza 15000
To Drop a Column:
The table should not be defined with a compact storage option. Syntax:
ALTER table_name DROP column_name;
Example 1: Employees table before alteration:
id name salary city 1 Adi 50000 Delhi 2 Bruno 30000 London 3 Chris 60000 New York 4 Davis 20000 New York 5 Eliza 15000 London
Query:
ALTER TABLE employees
DROP city; |
Explanation: The ’employees’ is an already created table. A column named ‘city’ is dropped from the table. All the data of the column is thus also dropped. Verify it by using the below command. SELECT * FROM employees; Output:
id name salary 1 Adi 50000 2 Bruno 30000 3 Chris 60000 4 Davis 20000 5 Eliza 15000
Example 2: Employees table before alteration:
id name salary 1 Adi 50000 2 Bruno 30000 3 Chris 60000 4 Davis 20000 5 Eliza 15000
Query:
ALTER TABLE employees DROP (name, salary); |
Explanation: The ’employees’ is an already created table. Here two columns named ‘name’ and ‘salary’ are dropped from the table. All the data of the columns is thus also dropped. Verify it by using the below command. SELECT * FROM employees;
Output:
id 1 2 3 4 5 |
