cyclist_id

Composite partition key example with a clustering column.

source '0_create_keyspace.cql'

DROP TABLE IF EXISTS cycling.cyclist_id;

// Find a cyclist's id given lastname and firstname
// Another CREATE TABLE using COMPOSITE PARTITION KEY
// 2i INDEX ALSO GOOD FOR THIS TABLE

// START-comp_pk
CREATE TABLE cycling.cyclist_id (
   lastname text,
   firstname text,
   age int,
   id UUID,
   PRIMARY KEY ((lastname, firstname), age) );
// END-comp_pk

// START-altercdc
ALTER TABLE cycling.cyclist_id
WITH CDC = false;
// END-altercdc

INSERT INTO cycling.cyclist_id (lastname, firstname, age, id) VALUES ('EENKHOORN','Pascal',18, ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156);
INSERT INTO cycling.cyclist_id (lastname, firstname, age, id) VALUES ('WELTEN','Bram',18, 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503);
INSERT INTO cycling.cyclist_id (lastname, firstname, age, id) VALUES ('COSTA','Adrien',17, 15a116fc-b833-4da6-ab9a-4a7775752836);

// START-resetid
UPDATE cycling.cyclist_id 
SET id = UUID() 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' AND age = 18
IF EXISTS;
// END-resetid

// START-ifid
UPDATE cycling.cyclist_id 
SET id = UUID() 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' AND age = 18
IF id = 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503;
// END-ifid

// START-ifnull
UPDATE cycling.cyclist_id 
SET id = UUID() 
WHERE lastname = 'Smith' AND firstname = 'Joe' AND age = 22
IF id = NULL;
// END-ifnull

 

Queries

SOURCE 'cyclist_id-table.cql';

// START-basicwhere
SELECT firstname AS 'First', lastname AS 'Last' FROM cycling.cyclist_id WHERE
lastname = 'COSTA' AND firstname = 'Adrien';
// END-basicwhere

// START-in_where
SELECT firstname AS 'First', lastname AS 'Last' FROM cycling.cyclist_id WHERE
lastname in ('COSTA','WELTEN');
// END-select_in_where

Composite partition key example without a clustering column.

source '0_create_keyspace.cql'

// Composite partition key example without a clustering column


DROP TABLE IF EXISTS cycling.cyclist_id;

// create table without age as clustering column
CREATE TABLE cycling.cyclist_id (
    lastname text,
    firstname text,
    age int,
    id uuid,
    PRIMARY KEY ((lastname, firstname))
    );
    
// insert data    
INSERT INTO cycling.cyclist_id (lastname, firstname, age, id) VALUES ('EENKHOORN','Pascal',18, ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156);
INSERT INTO cycling.cyclist_id (lastname, firstname, age, id) VALUES ('WELTEN','Bram',18, 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503);
INSERT INTO cycling.cyclist_id (lastname, firstname, age, id) VALUES ('COSTA','Adrien',17, 15a116fc-b833-4da6-ab9a-4a7775752836);

// attempt to delete data using IF EXISTS, applied false because Bram Jones does not exist
// START-deleteifexists
DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'JONES' and firstname = 'Bram' 
IF EXISTS;
// END-deleteifexists

// delete data using an IF, age does not match so applied is false
// START-deletedataif
DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' 
IF age = 20;
// END-deletedataif