Creating a table with a simple primary key

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

Data is stored in tables composed of rows and columns. A simple primary key consists of one column, which acts as the partition key. The partition key determines which node stores the data.

In this section, you will learn how to use these CQL statements:

CREATE TABLE to create a table.
INSERT to add rows to a table.
SELECT to retrieve rows from a table.

The examples in this section use a table named cyclist_name with these columns:

An ID number for each cyclist, which are stored in a universally unique identifier UUID column named id. The id column is the primary key for the table.
The cyclist's first and last names, which are stored in text columns named first_name and last_name.

The following examples show the CQL statements:

This CREATE TABLE statement creates the cyclist_name table in the cycling keyspace:

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  last_name text,
  first_name text
);

These INSERT statements add rows to the table. The column names and corresponding values are specified in the statements.

INSERT INTO cycling.cyclist_name (
  id, last_name, first_name
) VALUES (
  e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN', 'Anna'
);

INSERT INTO cycling.cyclist_name (
  id, last_name, first_name
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME', 'Alex'
);

INSERT INTO cycling.cyclist_name (
  id, last_name, first_name
) VALUES (
  220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO', 'Paolo'
);

INSERT INTO cycling.cyclist_name (
  id, last_name, first_name
) VALUES (
  6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK', 'Steven'
);

INSERT INTO cycling.cyclist_name (
  id, last_name, first_name
) VALUES (
  fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael'
);

This SELECT statement retrieves the rows from the table. The column names and values are returned.

SELECT *
FROM cycling.cyclist_name;

 id                                   | first_name | last_name
--------------------------------------+------------+-----------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |       Alex |           FRAME
 fb372533-eb95-4bb4-8685-6ef61e994caa |    Michael |        MATTHEWS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |      Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |     Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |       Anna | VAN DER BREGGEN

(5 rows)

This SELECT statement uses a WHERE clause to limit the rows retrieved. The WHERE clause returns the row with the specified id value.

SELECT *
FROM cycling.cyclist_name
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

 id                                   | first_name | last_name
--------------------------------------+------------+-----------
 fb372533-eb95-4bb4-8685-6ef61e994caa |    Michael |  MATTHEWS

(1 rows)