Creating a table with a compound primary key

Create a table with a compound primary key and populate it with data.

This section shows how to create a table with a compound primary key and populate it with data.

A compound primary key has a partition key and one or more clustering columns.
The partition key determines the node that stores the data.
The clustering columns specify the order of the rows in the partition.
When rows for a partition key are stored based on the clustering column values, row retrieval is fast. If the data is distributed throughout the nodes, data retrieval from a large partition can be slow when the entire partition must be read.

The examples in this section use a table named cyclist_category. The table has these columns:

Race category, which is the partition key.
Race points, which is the clustering column.
Cyclist id.
Cyclist lastname.

You can query the cyclist_category table to retrieve a list of cyclists and their race points in a category.

This CREATE TABLE statement uses the WITH CLUSTERING ORDER BY clause to sort the race points in descending order for each category:
```
CREATE TABLE cycling.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. You can add more than one row with the same category to the table as long as the rows contain different points values.

These INSERT statements add rows to the table:

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'GC', 1269, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO'
);

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'One-day-races', 367,220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO'
);

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'Time-trial', 182, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO'
);

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'Sprint', 0, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO'
);

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'GC', 1324, 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIJSWIJK'
);

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'One-day-races', 198, 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIJSWIJK'
);

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'Sprint', 39, 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIJSWIJK'
);

INSERT INTO cycling.cyclist_category (
  category, points, id, lastname
) VALUES (
  'Time-trial', 3, 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIJSWIJK'
);

This SELECT statement retrieves rows from the table. Within each category the rows are sorted in descending points order, which means that the highest points are listed first.

SELECT *
FROM cycling.cyclist_category;

 category      | points | id                                   | lastname
---------------+--------+--------------------------------------+------------
 One-day-races |    367 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
 One-day-races |    198 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
    Time-trial |    182 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
    Time-trial |      3 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
        Sprint |     39 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
        Sprint |      0 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
            GC |   1324 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
            GC |   1269 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO

(8 rows)