Static Columns: One Value per Partition

The rows in one partition often share a value. All the employees in a department share the same department head. You could copy the head's name onto every row, but then a change means updating every row.

A static column solves this. It stores one value per partition, and every row in that partition shows it.

Syntax

Add the word STATIC after the column type:

CREATE TABLE company (
  dept text,
  emp_id int,
  dept_head text STATIC,
  emp_name text,
  PRIMARY KEY (dept, emp_id)
);

dept is the partition key, so each department is one partition. dept_head belongs to the partition, not to a single row.

Example: set the head of Sales

Add two Sales employees:

INSERT INTO company (dept, emp_id, emp_name)
VALUES ('Sales', 3, 'John');
INSERT INTO company (dept, emp_id, emp_name)
VALUES ('Sales', 6, 'Frank');

Now set the head of Sales. The WHERE clause has only the partition key, with no emp_id, because you are writing to the partition itself:

UPDATE company SET dept_head = 'Lucy'
WHERE dept = 'Sales';

SELECT * FROM company WHERE dept = 'Sales';

Output:

 dept  | emp_id | dept_head | emp_name
-------+--------+-----------+----------
 Sales |      3 |      Lucy |     John
 Sales |      6 |      Lucy |    Frank

(2 rows)

One update, and both rows show Lucy. There is only one copy of the value, so it can never be different on two rows.

If you add emp_id to the WHERE clause, Cassandra refuses, because a static column does not belong to one row:

UPDATE company SET dept_head = 'Lucy'
WHERE dept = 'Sales' AND emp_id = 3;

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid restrictions on clustering columns since the UPDATE statement modifies only static columns"

When to use static columns

Use a static column for data about the "parent" that all rows in a partition share:

  • orders partitioned by customer, with the customer's name as a static column
  • chat messages partitioned by room, with the room's topic as a static column

A static column only makes sense in a table with clustering columns. Without them, each partition has just one row, so there is nothing to share:

CREATE TABLE teams (
  id int PRIMARY KEY,
  leader text STATIC
);

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Static columns are only useful (and thus allowed) if the table has at least one clustering column"

In the exercise, Grace is the new head of the IT department. Set it with one update.