Using a compound primary key

Use a compound primary key to create multiple columns that can be queried to return sorted results.

Use a compound primary key to create multiple columns that can be queried to return sorted results. If the pro cycling example was designed for a relational database, a cyclists table would be created with a foreign key to the races. In DataStax Enterprise, you denormalize the data because joins are not performant in a distributed system. Later, other schemas are shown that improve database performance.

Important: A NULL value cannot be inserted into a PRIMARY KEY column. This restriction applies to both partition keys and clustering columns.

Collections and indexes are two data modeling methods. This example creates a cyclist_category table storing a cyclist's lastname, ID, and points for each type of race category. The table uses category for the partition key and points for a single clustering column. This table can be queried to retrieve a list of cyclists and their points in a category, sorted by points.

A compound primary key table can be created in two different ways, as shown.

Procedure

To create a table that has a compound primary key, specify two or more columns as the primary key. This example defines category and points as the columns of the compound primary key. To sort the points in descending order, the example uses the additional WITH CLUSTERING ORDER BY clause. Ascending order is more efficient to store, but descending queries are faster due to the nature of the storage engine.
```
USE cycling;
CREATE TABLE IF NOT EXISTS cyclist_category ( 
  category text, 
  points int, 
  id UUID, 
  lastname text,     
  PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);
```
Note: The combination of the category and points uniquely identifies a row in the cyclist_category table. More than one row with the same category can exist as long as the rows contain different points values.
The keyspace name can be used to identify the keyspace in the CREATE TABLE statement instead of the USE statement.
```
CREATE TABLE IF NOT EXISTS cycling.cyclist_category (
  category text,
  points int,
  id UUID,
  lastname text,
  PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);
```
Note: In both of these examples, the points column is defined as a clustering column sorted in descending order.