Section 7: CQL Complete Guide

Master ALTER TABLE

Learn to modify Cassandra tables safely - add/drop columns, change options, with limitations, real examples, and best practices!

🎯 What is ALTER TABLE?

CRITICAL: ALTER TABLE is VERY Limited in Cassandra!

Unlike SQL databases, Cassandra has SEVERE restrictions on what you can alter. Most schema changes require creating a NEW table and migrating data!

⚠️ The Golden Rule:

You CANNOT change PRIMARY KEY after table creation!
If you need to change partition key or clustering columns → Create new table → Migrate data

What is ALTER TABLE Used For?

ALTER TABLE allows you to make minor modifications to an existing table structure:

Add new (non-primary-key) columns
Drop existing (non-primary-key) columns
Rename columns (limited scenarios)
Modify table options (TTL, compaction, etc.)
Change column data types (very limited)

🎨 ALTER TABLE Visual Guide

✅ What You CAN Alter

✅ ADD Regular Columns

Add non-primary-key columns anytime

ALTER TABLE users

  ADD phone_number TEXT;

// Existing rows: phone_number = NULL

✅ DROP Regular Columns

Remove non-primary-key columns

ALTER TABLE users

  DROP phone_number;

// Data deleted immediately

✅ RENAME Columns

Only for clustering columns

ALTER TABLE posts

  RENAME post_date TO created_at;

// Primary key columns only!

✅ MODIFY Table Options

Change TTL, compaction, etc.

ALTER TABLE logs

  WITH default_time_to_live = 86400;

// Change behavior anytime

🌐 How ALTER TABLE Propagates Across Cluster

❌ What You CANNOT Alter

ABSOLUTE Restrictions

These operations are IMPOSSIBLE in Cassandra. If you need them, create a new table!

❌ Change PRIMARY KEY

// ❌ IMPOSSIBLE!

ALTER TABLE users

  ADD new_id TO PRIMARY KEY;

ERROR: Cannot modify primary key!

Solution: Create new table with correct primary key

❌ Change Clustering Order

// ❌ IMPOSSIBLE!

ALTER TABLE posts

  WITH CLUSTERING ORDER BY (date ASC);

ERROR: Cannot change clustering order!

Solution: Create new table with correct order

❌ Change Column Type (Usually)

// ❌ IMPOSSIBLE!

ALTER TABLE users

  ALTER age TYPE BIGINT;

ERROR: Type changes not allowed!

Solution: Add new column, migrate data, drop old

❌ Rename Regular Columns

// ❌ USUALLY IMPOSSIBLE!

ALTER TABLE users

  RENAME email TO email_address;

ERROR: Only PK columns can rename!

Solution: Add new column, copy data, drop old

Why These Restrictions Exist

Cassandra's restrictions exist because of its distributed nature:

Primary Key: Determines data distribution across nodes (can't change without resharding ENTIRE cluster!)
Clustering Order: Data already sorted on disk (can't re-sort petabytes of data)
Column Type: Would require scanning/rewriting all data (too expensive)

💡 Design your schema carefully upfront - changes are expensive!

➕ ADD Column - Complete Guide

Basic Syntax

ADD Column Syntax

ALTER TABLE table_name

  ADD column_name datatype;

-- Add multiple columns at once

ALTER TABLE table_name

  ADD (column1 datatype1, column2 datatype2, ...);

-- Add static column

ALTER TABLE table_name

  ADD column_name datatype STATIC;

Real Examples

Example 1: Add Single Column

-- Original table

CREATE TABLE users (

  user_id UUID PRIMARY KEY,

  username TEXT,

  email TEXT

);

-- Add phone number column

ALTER TABLE users

  ADD phone_number TEXT;

-- Insert data with new column

INSERT INTO users (user_id, username, email, phone_number)

VALUES (uuid(), 'alice', 'alice@example.com', '+1-555-0100');

-- Existing rows: phone_number will be NULL

SELECT * FROM users WHERE user_id = ?;

// phone_number = NULL for old records

Example 2: Add Multiple Columns

ALTER TABLE users

  ADD (

    phone_number TEXT,

    address TEXT,

    country TEXT,

    last_login TIMESTAMP

  );

-- All new columns added at once

Example 3: Add Collection Column

ALTER TABLE users

  ADD favorite_tags SET<TEXT>;

-- Update with collection

UPDATE users

SET favorite_tags = {'sports', 'tech', 'gaming'}

WHERE user_id = ?;

Example 4: Add STATIC Column

-- Table with partition key and clustering column

CREATE TABLE user_posts (

  user_id UUID,

  post_id TIMEUUID,

  content TEXT,

  PRIMARY KEY (user_id, post_id)

);

-- Add STATIC column (shared across all posts per user)

ALTER TABLE user_posts

  ADD user_display_name TEXT STATIC;

-- Static column same for all posts by same user

UPDATE user_posts

SET user_display_name = 'Alice Smith'

WHERE user_id = ?;

// Now ALL posts by this user show same display name!

Important Notes on ADD Column

NULL Values: Existing rows will have NULL for new columns (no retroactive population)
No Default Values: Cassandra doesn't support DEFAULT values like SQL
Performance: Adding columns is instant (schema-only change, no data rewrite)
Storage: NULL columns don't consume disk space (sparse storage)

🎬 ADD Column Animation - What Happens

➖ DROP Column - Complete Guide

Basic Syntax

DROP Column Syntax

ALTER TABLE table_name

  DROP column_name;

-- Drop multiple columns

ALTER TABLE table_name

  DROP (column1, column2, column3);

Example: Drop Columns

-- Drop single column

ALTER TABLE users

  DROP phone_number;

-- Drop multiple columns at once

ALTER TABLE users

  DROP (address, country, postal_code);

-- IMPORTANT: Data is IMMEDIATELY deleted!

// No recovery possible after DROP!

DANGER: DROP is Permanent!

Immediate Deletion: Data deleted instantly across entire cluster
No Undo: Cannot recover dropped columns
No Confirmation: Cassandra won't ask "Are you sure?"
Production Risk: Always backup before dropping columns in production!

⚠️ DROP Column Animation - DANGER!

What You CANNOT Drop

❌ Primary Key Columns: Cannot drop partition key or clustering columns
❌ Last Regular Column: Table must have at least one non-PK column

-- ❌ Cannot drop primary key column

ALTER TABLE users

  DROP user_id;  -- ERROR!

-- ❌ Cannot drop clustering column

ALTER TABLE posts

  DROP post_date;  -- ERROR!

🔄 RENAME Column - Very Limited!

RENAME is VERY Restricted

You can ONLY rename PRIMARY KEY columns (partition key and clustering columns). Regular columns CANNOT be renamed!

Syntax & Examples

RENAME Syntax

-- Rename single column

ALTER TABLE table_name

  RENAME old_name TO new_name;

-- Rename multiple columns

ALTER TABLE table_name

  RENAME old1 TO new1

  AND old2 TO new2;

✅ CAN Rename: Clustering Column

CREATE TABLE posts (

  user_id UUID,

  post_date DATE,

  content TEXT,

  PRIMARY KEY (user_id, post_date)

);

-- ✅ WORKS!

ALTER TABLE posts

  RENAME post_date TO created_at;

❌ CANNOT Rename: Regular Column

-- ❌ FAILS!

ALTER TABLE posts

  RENAME content TO post_content;

ERROR: Cannot rename non-PK columns!

// Workaround:

ALTER TABLE posts ADD post_content TEXT;

// Copy data manually

ALTER TABLE posts DROP content;

When to Use RENAME

RENAME is useful when:

✓ Standardizing column names across tables
✓ Fixing typos in PRIMARY KEY columns
✓ Aligning with naming conventions

But: Use sparingly! It's safer to get names right during CREATE TABLE.

🔧 ALTER Column Type - Almost Impossible!

Type Changes Are 99% Forbidden

Cassandra does NOT support changing column data types in almost all cases. The only exceptions are extremely rare compatibility scenarios.

❌ This Does NOT Work

-- ❌ WILL FAIL in 99% of cases

ALTER TABLE users

  ALTER age TYPE BIGINT;

ERROR: Cannot change column type!

-- ❌ Also fails

ALTER TABLE users

  ALTER email TYPE VARCHAR;

ERROR: Type alteration not allowed!

Workaround for Type Changes

Step-by-step process:

// Step 1: Add new column with desired type

ALTER TABLE users

  ADD age_new BIGINT;

// Step 2: Copy data from old to new column

// (Must be done application-side, not in CQL)

UPDATE users

SET age_new = age  -- Copy each row

WHERE user_id = ?;

// Step 3: Drop old column

ALTER TABLE users

  DROP age;

// Step 4: Rename new column to old name (if it's PK)

ALTER TABLE users

  RENAME age_new TO age;  -- Only works if PK

Better Solution: Create New Table

For significant type changes, it's cleaner to:

Create new table with correct types
Write application code to write to BOTH tables temporarily
Backfill old data to new table
Switch application to use new table
Drop old table

🔄 Type Change Workaround - Step by Step

⚙️ Modify Table Options

Good News: Options Are Easy to Change!

Unlike structural changes, you can freely modify table options like TTL, compaction, compression, etc.

Common Option Changes

Change TTL (Time To Live)

-- Set TTL to 30 days

ALTER TABLE logs

  WITH default_time_to_live = 2592000;

-- Disable TTL

ALTER TABLE logs

  WITH default_time_to_live = 0;

-- Now data auto-expires after 30 days

Change Compaction Strategy

-- Switch to TimeWindowCompactionStrategy (for time-series)

ALTER TABLE sensor_data

  WITH compaction = {

    'class': 'TimeWindowCompactionStrategy',

    'compaction_window_unit': 'DAYS',

    'compaction_window_size': '1'

  };

-- Switch to LeveledCompactionStrategy (for read-heavy)

ALTER TABLE user_profiles

  WITH compaction = {

    'class': 'LeveledCompactionStrategy'

  };

Change Compression

-- Enable LZ4 compression

ALTER TABLE large_data

  WITH compression = {

    'sstable_compression': 'LZ4Compressor'

  };

-- Disable compression (not recommended)

ALTER TABLE large_data

  WITH compression = {

    'enabled': 'false'

  };

Change GC Grace Seconds

-- Set GC grace period to 7 days

ALTER TABLE users

  WITH gc_grace_seconds = 604800;

-- Important for tombstone cleanup

Change Comment

-- Update table comment

ALTER TABLE users

  WITH comment = 'User profiles with extended attributes';

When Option Changes Take Effect

Immediate: TTL, comment, caching
Next Compaction: Compression, compaction strategy
Gradual: GC grace (affects future tombstones)

💡 Real-World ALTER TABLE Scenarios

Scenario 1: Adding User Preferences

Situation: Need to track user preferences that weren't in original design

-- Original table

CREATE TABLE users (

  user_id UUID PRIMARY KEY,

  username TEXT,

  email TEXT

);

-- Add preferences columns

ALTER TABLE users

  ADD (

    language TEXT,

    timezone TEXT,

    notification_enabled BOOLEAN,

    theme TEXT

  );

-- Update existing users with defaults

UPDATE users

SET language = 'en',

    timezone = 'UTC',

    notification_enabled = true,

    theme = 'light'

WHERE user_id = ?;

Scenario 2: Implementing Data Retention

Situation: Need to auto-delete logs after 90 days for GDPR compliance

-- Existing logs table with no TTL

CREATE TABLE application_logs (

  app_id TEXT,

  log_time TIMESTAMP,

  message TEXT,

  PRIMARY KEY (app_id, log_time)

);

-- Add TTL for GDPR compliance (90 days = 7776000 seconds)

ALTER TABLE application_logs

  WITH default_time_to_live = 7776000;

-- Also switch to time-window compaction for efficiency

ALTER TABLE application_logs

  WITH compaction = {

    'class': 'TimeWindowCompactionStrategy',

    'compaction_window_unit': 'DAYS',

    'compaction_window_size': '1'

  };

Scenario 3: Performance Optimization

Situation: Table has high read volume, need better compaction strategy

-- User profiles with heavy reads

CREATE TABLE user_profiles (

  user_id UUID PRIMARY KEY,

  data TEXT

) WITH compaction = {'class': 'SizeTieredCompactionStrategy'};

-- Switch to LeveledCompactionStrategy for read-heavy workload

ALTER TABLE user_profiles

  WITH compaction = {

    'class': 'LeveledCompactionStrategy'

  };

-- Enable caching for hot data

ALTER TABLE user_profiles

  WITH caching = {

    'keys': 'ALL',

    'rows_per_partition': '100'

  };

Scenario 4: Schema Evolution (Safe Migration)

Situation: Need to change column from INT to BIGINT (not directly supported)

-- Step 1: Add new column with correct type

ALTER TABLE metrics

  ADD view_count_new BIGINT;

-- Step 2: Dual-write application (write to both columns)

UPDATE metrics

SET view_count = ?,

    view_count_new = ?

WHERE metric_id = ?;

-- Step 3: Backfill old data (application-side)

// Copy view_count to view_count_new for all existing rows

-- Step 4: Verify all data migrated

SELECT COUNT(*) FROM metrics WHERE view_count_new IS NULL ALLOW FILTERING;

-- Step 5: Switch application to use new column

// Update all queries to use view_count_new

-- Step 6: Drop old column after grace period

ALTER TABLE metrics

  DROP view_count;

✅ ALTER TABLE Best Practices

✅ DO These Things

✓ Test ALTER in dev environment first
✓ Backup data before schema changes
✓ Add columns instead of changing types
✓ Use meaningful column names from start
✓ Document all schema changes
✓ Plan migration strategy for big changes
✓ Use table options freely
✓ Monitor performance after changes

❌ DON'T Do These

✗ Don't ALTER primary key (impossible!)
✗ Don't DROP columns in production without backup
✗ Don't expect SQL-like ALTER flexibility
✗ Don't change types directly
✗ Don't rename regular columns
✗ Don't forget NULL handling for new columns
✗ Don't rush schema changes
✗ Don't skip testing migrations

Golden Rules for ALTER TABLE

Design Carefully Upfront: ALTER TABLE is limited, so get schema right in CREATE TABLE
Always Test First: Run ALTER in dev/staging before production
Backup Before DROP: Dropped data cannot be recovered
Migrate Don't Mutate: For major changes, create new table and migrate
Document Everything: Track all schema changes with dates and reasons
Gradual Rollout: Use dual-write patterns for safe migrations

When to Avoid ALTER and Create New Table Instead

Create a new table when:

Need to change primary key structure
Need to change clustering order
Need to change multiple column types
Want to completely redesign data model
Current table has performance issues due to design

💡 It's often cleaner to create a new table with correct design than force migrations!

Responsive Ad